Resizing Scroll Bars

[cpu97]

Hello to all,

I have been trying to figure out how to resize the scroll bars on the bottom or side of a worksheet in Excel 2007.

Excel modifies the scroll bars when I paste in a dataset which requires smaller scroll bars, but it does not seem to work the other way. For instance, I might have tens of thousands of rows of data and the scroll bar is sized accordingly (smallest possible size and dragging it a hair moves hundreds of rows at a time). If I delete most of the data, however, and end up with a few hundred rows, I'd like to resize that scroll bar such that it is appropriate for the number of rows. Beyond copying and pasting the reduced data into a new sheet (which will adjust the scroll bar sizes accordingly), does anyone know a way to do this? I think the solution may have something to do with redefining the outer limits (row and column) of the sheet, but I don't know how to go about doing that.

Many thanks for your consideration of this question.

Chris

 

[tlowry]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--> You can change the size and other properties with a macro
Below is an example.

There are lots of other properties you can change, but these seem to be the ones you should look at first

]------------------------------------------------------
]------------------------------------------------------
]------------------------------------------------------
Sub resizeSB()
With Sheets(1).Shapes("Scroll Bar 1")
.Height = 100
.Width = 0.5
.ControlFormat.Max = 1000
.ControlFormat.SmallChange = 100
End With
End Sub
]------------------------------------------------------
]------------------------------------------------------
]------------------------------------------------------

 

[cpu97]

Thank you, tlowry. Unfortunately this did not work as the program could not identify the "Scroll Bar 1" shape ("the item with the specified name wasn't found"). Perhaps there is another name for it which I could not find. I tried "ScrollBar1" to no avail. I tried looking under "Properties" in the Developer tab in Excel to see if I could find the correct name for it, but was unable to find it in the list of properties. Is there another way to find the name of the scroll bar in order to correctly reference it?

 

[tlowry]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]-->

• with the scroll bar displayed
• record a macro Tools|Macro|Record New Macro (remember the macro name)
• Left click the scroll bar
• stop the recording of the macro Tools|Macro|Stop Recording
• Edit the macro Tools|Macro|Macros|<select macro from list> press Edit
• There should be a line that looks like:

ActiveSheet.Shapes("Scroll Bar 1").Select

 

[cpu97]

Thank you, but unfortunately the left click on the scroll bar itself regsitered no code in the macro, so this does not give me any clue as to the correct name of the object. I also tried clicking below and above the scroll bar, and the macro recorded "ActiveWindow.LargeScroll Down:=1", which leads me to believe that activity recorded was a change of view as opposed to the modification of properties or position of the scroll bar itself (I may be off on some of the terminology - sorry). Thank you for your attempts!

 

[meldoc]

Hello

See if this helps.

http://support.microsoft.com/kb/244435

 

[cpu97]

Thanks, Brian! It worked. The key is deleting and then saving- that resized the scroll bars.