Resizing Scroll Bars

cpu97

Board Regular
Joined
Jan 8, 2010
Messages
62
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
<!--[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

Board Regular
Joined
Jan 8, 2010
Messages
62
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

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
<!--[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

Board Regular
Joined
Jan 8, 2010
Messages
62

ADVERTISEMENT

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!
 

cpu97

Board Regular
Joined
Jan 8, 2010
Messages
62
Thanks, Brian! It worked. The key is deleting and then saving- that resized the scroll bars.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top