I often wonder why there is no provision to adjust the maximum value of a scroll bar in Excel based on a cell value. Had this been available, a scroll bar would automatically adjust its maximum value when the cell value changes.
Something, as shown below, could then have been possible:
Until the time it is not made available by the Microsoft Office team, this tip can help you link the maximum value for a scroll bar to a cell.
Adjust Scroll Bar Maximum Value in Excel
- Go to Developer Tab –> Insert –> ActiveX Controls –> Scroll Bar (ActiveX Control)
- Do not have the developer tab?? Read here on how to add the developer tab to the ribbon.
- Click Anywhere in your worksheet to insert the Scroll Bar.
- Right-click on the Scroll Bar and select Properties.
- Set the linked cell as C10 and close the properties box.
- I have used C10 in this example. You can have a different cell where you have the maximum value.
- Double Click on the Scroll Bar. It will open the VBA Editor with a code that looks like this:
Private Sub ScrollBar1_Change()
End Sub
- Add a line so that your code looks like this:
Private Sub ScrollBar1_Change()
Activesheet.Scrollbar1.Max = Range("C7").Value
End Sub
- Close the VBA Editor window.
- Go to the Developer tab and left-click on Design Mode button.
- That’s It!! You Scroll Bar is all set to be used.
Note that since the file has a macro, you need to save the file in either .xls or .xlsm format.
Once you are done setting this up, and IF the scroll bar is not working, follow these steps:
- Go to Developer Tab click on Design Mode.
- Double click on the scroll bar.
- In the VBA Editor, Press F5, or click on the Run Sub/User form button.
- Close the VBA Editor.
Try it yourself.. Download the file
You May Also Like the Following Excel Tutorials:
2 thoughts on “Adjust Scroll Bar Maximum Value based on a Cell Value in Excel”
Nice tip
Hi great tutorial, if I wanted the scroll bar to always display newest data added at the bottom of the scroll whenever added. How is this possible?