code to change chart axes

[gkis2]

Code which I had in another (very similar) Excel project suddenly doesn't work, very simple code. Purpose is to set the min and max value for the X axis of a chart. There is only one chart on the sheet, I tried to loop through all charts in case I had the chart identified incorrectly somehow. the error that I get is 13: type mismatch. For Each cht … is highlighted. I tried it with the for each loop commented out and nothing happens, no errors but no change in the chart either.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Button4_Click()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim cht As Chart[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]For Each cht In ActiveSheet.ChartObjects[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Set cht = ActiveSheet.ChartObjects("Chart 1").Chart[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'Adjust x-axis Scale
cht.Axes(xlCategory).MinimumScale = 0
cht.Axes(xlCategory).MaximumScale = 90

Next cht[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub[/FONT]

 

[Repush]

try:

Sub Button4_Click()
    Dim cht As Chart
    nCh = ActiveSheet.ChartObjects.Count
    For i = 1 To nCh
        Set cht = ActiveSheet.ChartObjects(i).Chart
        'Adjust x-axis Scale
        cht.Axes(xlCategory).MinimumScale = 0
        cht.Axes(xlCategory).MaximumScale = 90
    
    Next i
End Sub

 

[Domenic]

The reason you're getting a type mismatch error is that you're looping through each ChartObject object , while you declared your For Each control variable as a Chart object. Therefore, your macro should be amended as follows...

Sub Button4_Click()
    Dim chtObj As ChartObject
    Dim cht As Chart
    For Each chtObj In ActiveSheet.ChartObjects
        Set cht = chtObj.Chart
        'Adjust x-axis Scale
        With cht
            .Axes(xlCategory).MinimumScale = 0
            .Axes(xlCategory).MaximumScale = 90
        End With
    Next chtObj
End Sub

Hope this helsp!

 

[gkis2]

Works perfectly, so I see you looped through all charts, you just did it in a way that works. So that must mean the Chart is not "Chart1" anymore? If so, any ideas on how to figure out what it is now? Now I think about it, I did paste the chart in from the other project that I was talking about. That's why I thought I could just copy the code in as well. Ideally, if I knew how, I could give the chart object a name that makes sense to its purpose.

 

[Domenic]

If so, any ideas on how to figure out what it is now?

Just in case you missed it, see my post in this thread, the one I posted just before you posted your message.

 

[Repush]

if you hover above the graph it should reveal its name . . . or check the selection window (binoculars on start tab, at the bottom)

 

[gkis2]

OK, its Chart3. I guess I can change it later. For right now, it ain't broke and I ain't fixin it. BTW, If I have another Chart on another tab, would this code affect that chart as well?

 

[Domenic]

It will only affect charts on the active sheet.

 

[gkis2]

Thank You Sir