Select first chart in sheet
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 |
Select first chart in sheet
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 |
Select first chart in sheet
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 |
Select first chart in sheet
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 |
All times are GMT +1. The time now is 08:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com