Adjust Scroll Bar Maximum Value based on a Cell Value in Excel

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

1. 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.

2. Click Anywhere in your worksheet to insert the Scroll Bar.
3. Right-click on the Scroll Bar and select Properties.
4. 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.

5. 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

6. Add a line so that your code looks like this:

Private Sub ScrollBar1_Change()
Activesheet.Scrollbar1.Max = Range("C7").Value
End Sub

7. Close the VBA Editor window.
8. Go to the Developer tab and left-click on Design Mode button.
9. 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:

• How to Create Dynamic Labels in Excel Scroll Bar.
• How to Insert and Use Check Boxes in Excel.
• How to Insert and Use Radio Buttons in Excel.
• Free Online Excel Training (7 Part Online Video Course).
• Insert Check Mark (Tick ✓) Symbol in Excel