Excel VBA: update graph through a function v2

Luis Diaz
Luis Diaz used Ask the Experts™
Hello experts,

I have the following attached file (Timeline-V3.xlsm) and I would like to clean and align with the solution proposed at:
https://www.experts-exchange.com/questions/29168471/Excel-VBA-update-graph-through-a-function.html#a43008978
The needs are the following
-Remove hidden rows from 1 to 39
-Remove hidden columns and just keep for the graph Event column E, Duration column H and Height column J (already bold)
The aim of this is to have exactly the same graph and data input requirement for Yearly and Daily timeline.
I also attached file to take as a reference for the alignement (ref_29168471_Yearly-timeline-template.xlsmj)
If you have questions, please contact me.
Thank you for your help.
Timeline-v3.xlsm
ref_29168471_Yearly-timeline-templa.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Luis DiazIT consultant

Author

Commented:
Great, it works!
Last questions
Can I get remove column A & B, I supposed that Yes, but I would like to have confirmation?
In order to have the approach of data labels title + time displayed I removed the comment related of the following block
If MajorUnit < 1 Then
            .Text = rng.Offset(, 1).Cells(iLbl)
            .Text = rng.Offset(, 1).Cells(iLbl) & ", " & Format(rng.Cells(iLbl), "hh:mm")
          Else
            .Text = rng.Offset(, 1).Cells(iLbl)
            .Text = rng.Offset(, 1).Cells(iLbl) & ", " & rng.Cells(iLbl)
          End If
          

Open in new window


However I don't understand why I have an error.
In order to have Event + Date entered for data labels how should I proceed? Possible to have a last file with this approach?

Thank you in advance for your help.
If column A and B are removed, the macro must be adjusted to address the correct cells.

The error on the block is because the graph range is from row 6 to 418, but the label from row 18 down is empty, and can not be formatet.
Only the last line .Text is needed

Added a check that the label is not empty
          If Len(rng.Offset(, 1).Cells(iLbl)) > 0 Then
            With lbl
              If MajorUnit < 1 Then
                '.Text = rng.Offset(, 1).Cells(iLbl)
                .Text = rng.Offset(, 1).Cells(iLbl) & ", " & Format(rng.Cells(iLbl), "hh:mm")
              Else
                '.Text = rng.Offset(, 1).Cells(iLbl)
                .Text = rng.Offset(, 1).Cells(iLbl) & ", " & rng.Cells(iLbl)
              End If
              .Position = xlLabelPositionLeft
            End With
          End If

Open in new window

Timeline-v3.xlsm
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Luis DiazIT consultant

Author

Commented:
Thank you. I will test it and keep you informed.
Luis DiazIT consultant

Author

Commented:
Hello Ejgil,
I tested and it works !
1.This approach is interesting and I was wondering if we can set up a flag for data labels which include date.
The idea is the following if:
-If this value is equal to 1 data labels will be composed by Event + date if is equal to 0 or other it will be composed just by Event (as in previous version).
Value to report next to Unit table
20200115_062739-screenshot.png2.Given this new modification I was wondering if we can also align yearly timeline with the same process:
-Column A + B removal
-Flag Event + date
I attached last files version to update.
Thank you in advance for your help.
Daily-timeline-flag-data-labels.xlsm
Yearly-timeline-flag-data-labels.xlsm
That should be possible.

Please accept when a question is answered.
Open a new question with a link from here, instead of adding new requirements several times.
Luis DiazIT consultant

Author

Commented:
Noted. Thank you.
Luis DiazIT consultant

Author

Commented:
I created new question as recommended:
Please find bellow the link: https://www.experts-exchange.com/questions/29169684
Thank you for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial