Automated Gantt Chart

[riedyp]

Hello,
I am trying to make a gantt chart that will automatically update when the data is changed. More specifically I would like the bounds of the start and end date refer to the "Min Start" and "Max End" dates. I also am trying to get the data to only capture real values and not blank rows so it can be updated very easily but not sure if this is possible. The white chart references values in the yellow chart down to row 45. Please let me know if eithe rof these tasks are even possible, thank you.

 

[Worf]

This code allows you to automatically update start and end dates:

' sheet module
Private Sub Worksheet_Change(ByVal Target As Range) ' cells J17 and K17
Dim ch As ChartObject
If Target = [j17] Or Target = [k17] Then
    Set ch = ActiveSheet.ChartObjects("chart 4")
    ch.Chart.Axes(xlValue).MinimumScale = [j17]
    ch.Chart.Axes(xlValue).MaximumScale = [k17]
End If
End Sub

 

[riedyp]

Thank you, the code worked the first time i changed my values and it updated. However, when I switched my values back the chart did not update and now the code seems to get stuck. Do you have any suggestions for what may be the issue?

 

[Worf]

• Remove any break points at VBE > Debug > clear all break points.
• Note that this code is supposed to work automatically when one of the two cells changes, without any button clicking or manual macro execution.

If necessary, I can post my test workbook.

 

[riedyp]

I removed the breakpoints, but it is still not updating.
S2=min(S4:S40)
T2=min(T2:T40)
I am wondering if these having formulas are why it isn't updating since the formula remains the same but it is only displaying a different output.
I did not think that would make a difference but i am assuming that is the bug.

 

[Worf]

New version:

Private Sub Worksheet_Calculate()
Dim ch As ChartObject
Set ch = ActiveSheet.ChartObjects("chart 4")
ch.Chart.Axes(xlValue).MinimumScale = [j17]
ch.Chart.Axes(xlValue).MaximumScale = [k17]
End Sub

 

[riedyp]

Thank you, it now adjusts but if you look at the picture it offsets by just a bit much, any idea on how to adjust that?

 

[Worf]

Can you post a link to your test workbook, with sensitive data changed?