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:Adjust Scroll Bar Maximum Value based on a Cell Value in Excel Test2

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)

Adjust Scroll Bar Maximum Value based on a Cell Value in Excel Activex Control Insert

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

Adjust Scroll Bar Maximum Value based on a Cell Value in Excel Set Max Value Property

  1. 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
  1. Add a line so that your code looks like this:
Private Sub ScrollBar1_Change()
Activesheet.Scrollbar1.Max = Range("C7").Value
End Sub
  1. Close the VBA Editor window.
  2. Go to the Developer tab and left-click on Design Mode button.
  3. 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.

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

Try it yourself.. Download the file
Download File Pic

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

2 thoughts on “Adjust Scroll Bar Maximum Value based on a Cell Value in Excel”

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

Comments are closed.