VBA automatically change Min and Max Horizontal X Axis on Chart ? (code included)

[SereneSea]

Hello again VBA Neebie here,
I have another question now. I have a ganntt chart that I would like the x-axis to change with what is in cells H3 and I3. My minimum date start is in H3 and maximum is in I3.
If its possible to have the chart updated as soon as someone enters the dates?
I tried this code but I am getting an automation and unspecified error.

Sub ScaleXAxisWholeCycle()
    Dim Ch As Chart
    
    On Error Resume Next
    Set Ch = ActiveChart
    On Error GoTo 0
    
    If Not Ch Is Nothing Then
        With ActiveChart.Axes(xlCategory, xlPrimary)
            .MinimumScale = ActiveSheet.Range("H3").Value
            .MaximumScale = ActiveSheet.Range("I3").Value
        End With
    Else
        MsgBox "No Chart Found"
    End If
End Sub

 

[BSALV]

apparantly there was a mistake between the X and the Y-axis !
In the first posts it was ".Axes(xlCategory, xlPrimary)", now it's ".Axes(xlValue)

if you now use the change-event in your sheetmodule ( the sheet of H3, I3 and the chart)

Private Sub Worksheet_Change(ByVal Target As Range)
     If Intersect(Target, Range("H3:I3")) Is Nothing Then Exit Sub
     MsgBox "you changed H3 or I3", vbInformation

     With Me.ChartObjects("Chart 9").Chart                      'or the name of the chart or its' indexnumber
     'With .Axes(xlCategory)                                '---> was the wrong one ??? !!! ??? = IS REMARK NOW, choose 1 of these 2
          With .Axes(xlValue)                                   '--> is the good one
               .MinimumScale = Range("H3").Value
               .MaximumScale = Range("I3").Value
          End With
     End With
End Sub

 

[SereneSea]

It doe nothing

 

[SereneSea]

example with chart2.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1							2022						DO not delete Columns K to O please
2
3							Plan Start	3/1/2022	12/31/2022		% Complete	0%
4		Concat	Department	Test	Trainer	Trainee	Trainee Area	PRIORITY	START	PLAN END	ACTUAL END	% COMPLETE	Project Start-(chart start)	Days to Start	Complete	Incomplete	Plan Days
5		Unicorn (T: whiskers)	Alpha	Unicorn	whiskers	Bird	Delta	MEDIUM	4/5/2022	7/2/2022		0%	3/1/2022	35	0	88	88
6		Frog (T: Pluto)	Omega	Frog	Pluto	Whiskers	Omega	HIGH	4/5/2022	6/3/2022		0%	3/1/2022	35	0	59	59
7		Cat (T: Saturn)	Delta	Cat	Saturn	Pluto	Alpha	LOW	4/5/2022	6/3/2022		0%	3/1/2022	35	0	59	59
8
9
10
11
12
13
14
15
16
17
18
19
20					0%
21					25%
22					50%
23					75%
24					100%
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Entry

Cell Formulas
Range	Formula
M3	M3	=AVERAGE(Table1[% COMPLETE])
N5:N7	N5	=$I$3
O5:O7	O5	=IF(ISBLANK(J5),0,J5-N5)
P5:P7	P5	=$M5*$R5
Q5:Q7	Q5	=R5-P5
R5:R7	R5	=IF(ISBLANK(K5),0,K5-J5)
C5:C7	C5	=CONCAT([@Test]," (T: ",[@Trainer],")")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5	Cell Value	contains "LOW"	text	NO
I5	Cell Value	contains "MEDIUM"	text	NO
I5	Cell Value	contains "HIGH"	text	NO
M3	Other Type	Color scale		NO
M5:M8	Other Type	DataBar		NO
I4,I6:I7	Cell Value	contains "LOW"	text	NO
I4,I6:I7	Cell Value	contains "MEDIUM"	text	NO
I4,I6:I7	Cell Value	contains "HIGH"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I5:I7	List	HIGH,MEDIUM,LOW
M5:M7	List	=$F$20:$F$24

 

[BSALV]

nothing ? no msgbox neither ?
An extra msgbox before the intersect

Did you use other macros with a command like "Application.EnableEvents=false" ?

If you save & close your excelfile and reopen it immediately, and then change I3 or J3 (no H3) what happens ?

Private Sub Worksheet_Change(ByVal Target As Range)

     MsgBox Target.Address

     If Intersect(Target, Range("I3:J3")) Is Nothing Then Exit Sub
     MsgBox "you changed H3 or I3", vbInformation

     With Me.ChartObjects("Chart 9").Chart                      'or the name of the chart or its' indexnumber
     'With .Axes(xlCategory)                                '---> was the wrong one ??? !!! ??? = IS REMARK NOW, choose 1 of these 2
          With .Axes(xlValue)                                   '--> is the good one
               .MinimumScale = Range("i3").Value
               .MaximumScale = Range("j3").Value
          End With
     End With
End Sub

 

[SereneSea]

IT WORKS! I closed and reopened excel and it works now.! Thank you so much for you help

 

[BSALV]

great, nice weekend.

 

[SereneSea]

great, nice weekend.

Thank you! Have a great weekend as well.