Select first chart in sheet

G

Guest

I would like to select the first chart in the sheet and set the min and max
values... the code says an object is not set... any help is appreciated!

Dim MinVal As Range
Dim MaxVal As Range
Dim ch As Chart
Set MinVal = ActiveSheet.Range("o17").Value
Set MaxVal = ActiveSheet.Range("o16").Value
ch = ActiveSheet.ChartObjects(1).Chart


For Each ch In ActiveSheet.ChartObjects
Select Case ch.ChartType
Case xlLineMarkers
With ch.Axes(xlCategory)
.MinimumScale = MinVal
.MaximumScale = MaxVal
End With
End Select
Next
End Sub
 
J

JE McGimpsey

You seem to have your objects and variables confused.

"Set" is only used to assign objects to object or variant variables.
You're assigning cell values to MinVal and MaxVal, so they should not be
set to a range.

OTOH, you *are* attempting, for some reason, to assign a chart object
(but not a ChartObject) to the ChartObject variable ch. The error comes
from not using Set, but if you had, you'd get a type mismatch error.

But then you try to set ch to a ChartObject, not a Chart, so I suspect
you mean this instead:

Dim MinVal As Double
Dim MaxVal As Double
Dim ch As ChartObject
MinVal = ActiveSheet.Range("o17").Value
MaxVal = ActiveSheet.Range("o16").Value
For Each ch In ActiveSheet.ChartObjects
Select Case ch.Chart.ChartType
Case xlLineMarkers
With ch.Chart.Axes(xlCategory)
.MinimumScale = MinVal
.MaximumScale = MaxVal
End With
End Select
Next
 
G

Guest

JE I tried yours and it says unable to set the minimum scale for the axis...

Kelly I will take alook at that site!
 
G

Guest

changed one thing...
With ch.Chart.Axes(xlCategory) *** to xlValue
.MinimumScale = MinVal
.MaximumScale = MaxVal
End With

THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! for the help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top