Thread: VBA for setting max value in scroll bars on charts?

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!

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

In article <1153869805.562982.326180@m73g2000cwd.googlegroups.com>, Bob@
1800-mail.com says...
> 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!
>
>

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


Tushar Mehta wrote:
> 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
>
> In article <1153869805.562982.326180@m73g2000cwd.googlegroups.com>, Bob@
> 1800-mail.com says...
> > 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!
> >
> >

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?


Bob@1800-mail.com wrote:
> 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
>
>
> Tushar Mehta wrote:
> > 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
> >
> > In article <1153869805.562982.326180@m73g2000cwd.googlegroups.com>, Bob@
> > 1800-mail.com says...
> > > 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!
> > >
> > >