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

John -

I have an example similar to what you are trying to do. You may want to
take a look.
http://processtrends.com/pg_charts_s..._max_dates.htm

....Kelly







"John" wrote in message
...
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

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




In article ,
John wrote:

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

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

Kelly I will take alook at that site!



"JE McGimpsey" wrote:

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




In article ,
John wrote:

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

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

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


"John" wrote:

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

Kelly I will take alook at that site!



"JE McGimpsey" wrote:

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




In article ,
John wrote:

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