VBA for setting max value in scroll bars on charts?

[Bob]

I have 20 dynamic charts with scroll bars and I want to avoid manually
adjusting the maximum value each month on the 20 scroll bars.

Is there some VBA I could use to do this?

Thank you!

 

[Tushar Mehta]

Don't know if it will work in this case, but a good place to start is with
the macro recorder.

Turn it on (Tools | Macro > Record new macro...), do whatever it is you want
to do to one scrollbar by hand, turn off the recorder, and switch to the
VBE. Did XL generate the necessary code? If you are using the controls
from the Forms toolbar, it did. If from the Control Toolbox, it probably
didn't.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bob]

Thank you, that worked. Although there is probably a more elegant way
to write this with a loop, here's what I came up with below.

Also, thank you for your great web site.


Sheets("6").Select
ActiveChart.Shapes("Scroll Bar 6").Select
With Selection
.Max = .Value + 1
End With
Sheets("7").Select
ActiveChart.Shapes("Scroll Bar 5").Select
With Selection
.Max = .Value + 1
End With
End Sub

 

[Bob]

I ran into another problem where the scroll bars on various charts seem
to be using the same names and I need them to use unique names - I
can't have two "number 5" for example - is there any way to control the
names assigned to the scroll bars?