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