I have a spreadsheet to manage progress on a project. I am trying to use
multiple scroll bars (one per activity) to give a visual representation of
progress by changing the scrollbar position and colour from red to orange to
green according to progress vs target. Whilst my coding does work it is not
neat as I have to initialise and control each scrollbar indivdually (and
there are 60 + of them). Is there a way to reference them indirectly, some
sort of

For ShtNum = 1 to NumSheets
For i = 1 to sheet(shtNum).scrollbars.count
myscroll=Sheet(ShtNum).scrollbar(i)
If myscroll < 0.9 * target then
myscroll.backcolour = RGB(255,0,0)
Elseif myscroll < 0.95 * target then
myscroll.backcolour = RGB(200,200,0)
Else
myscroll.backcolour = RGB(0,255,0)
Endif
next i
Next ShtNum

I think my issue is that I am not sure which collection the scrollbars fall
into.

scrollbars from the forms toolbar are part of the scrollbars collection for
each sheet

scrollbars from the control toolbox toolbar are part of the OLEObjects
collection, but so are many other types of object.

Dim obj as OleObject
Dim scr as MSForms.Scrollbar
Dim sh as Worksheet
for each sh in Worksheets
for each obj in sh.OleObjects
if typeof Obj.Object is MSForms.Scrollbar then
set scr = obj.Object
if scr.Value/scr.max .9 then


--
Regards,
Tom Ogilvy


"Mark" wrote:

I have a spreadsheet to manage progress on a project. I am trying to use
multiple scroll bars (one per activity) to give a visual representation of
progress by changing the scrollbar position and colour from red to orange to
green according to progress vs target. Whilst my coding does work it is not
neat as I have to initialise and control each scrollbar indivdually (and
there are 60 + of them). Is there a way to reference them indirectly, some
sort of

For ShtNum = 1 to NumSheets
For i = 1 to sheet(shtNum).scrollbars.count
myscroll=Sheet(ShtNum).scrollbar(i)
If myscroll < 0.9 * target then
myscroll.backcolour = RGB(255,0,0)
Elseif myscroll < 0.95 * target then
myscroll.backcolour = RGB(200,200,0)
Else
myscroll.backcolour = RGB(0,255,0)
Endif
next i
Next ShtNum

I think my issue is that I am not sure which collection the scrollbars fall
into.

Thankyou kindly sir

"Tom Ogilvy" wrote:

scrollbars from the forms toolbar are part of the scrollbars collection for
each sheet

scrollbars from the control toolbox toolbar are part of the OLEObjects
collection, but so are many other types of object.

Dim obj as OleObject
Dim scr as MSForms.Scrollbar
Dim sh as Worksheet
for each sh in Worksheets
for each obj in sh.OleObjects
if typeof Obj.Object is MSForms.Scrollbar then
set scr = obj.Object
if scr.Value/scr.max .9 then


--
Regards,
Tom Ogilvy


"Mark" wrote:

I have a spreadsheet to manage progress on a project. I am trying to use
multiple scroll bars (one per activity) to give a visual representation of
progress by changing the scrollbar position and colour from red to orange to
green according to progress vs target. Whilst my coding does work it is not
neat as I have to initialise and control each scrollbar indivdually (and
there are 60 + of them). Is there a way to reference them indirectly, some
sort of

For ShtNum = 1 to NumSheets
For i = 1 to sheet(shtNum).scrollbars.count
myscroll=Sheet(ShtNum).scrollbar(i)
If myscroll < 0.9 * target then
myscroll.backcolour = RGB(255,0,0)
Elseif myscroll < 0.95 * target then
myscroll.backcolour = RGB(200,200,0)
Else
myscroll.backcolour = RGB(0,255,0)
Endif
next i
Next ShtNum

I think my issue is that I am not sure which collection the scrollbars fall
into.