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

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

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?

    Reply

Leave a Comment

FREE EXCEL E-BOOK

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

FREE EXCEL E-BOOK

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

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

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