Excel VBA Scroll Bar Form Control: Setup, Uses, and Code

In Excel for Microsoft 365 on Windows, insert a Form Control scroll bar from Developer > Insert > Form Controls, then link it to a worksheet cell in Format Control. The linked cell stores the scroll bar’s integer value; formulas or VBA can use that value to select a record, change a displayed result, or drive another part of a dashboard.

What an Excel Form Control Scroll Bar Does

A scroll bar is a worksheet control with a draggable thumb and increment buttons. Moving it changes a numeric value between a configured minimum and maximum. Unlike a worksheet’s vertical or horizontal scrolling, a Form Control scroll bar does not move the worksheet view. It is an input device for a value in a model or interface.

For example, a dashboard might use a scroll bar to choose one of 20 products. Its linked cell contains a number from 1 to 20, and a formula or macro uses that number to show the corresponding product. Another common use is to adjust a scenario input, such as a forecast month, quantity, or percentage stored as a whole number.

The Form Control version is distinct from an ActiveX scroll bar. Form Controls are generally simpler to insert and use; ActiveX controls offer additional properties and event options but depend on ActiveX support and have greater security and compatibility considerations. For a straightforward dashboard selector, a Form Control is usually the appropriate starting point.

Insert and Set Up the Scroll Bar

If the Developer tab is hidden in Excel for Windows, enable it through File > Options > Customize Ribbon, then select Developer. To add the control:

Choose Developer > Insert.
Under Form Controls, choose Scroll Bar. Be sure to select the Form Control icon, not an ActiveX control.
Drag on the worksheet to draw the control. Hold Alt while drawing or moving it to help align its edges with cell boundaries.
Right-click the scroll bar and choose Format Control.
On the Control tab, set the current value, minimum value, maximum value, incremental change, page change, and cell link. Choose OK.

The linked cell is the key connection between the control and the worksheet. For a dashboard, you might link it to B2 and give that cell a clear label such as Selected item number. The cell displays the current numeric position. It does not automatically display a text label or modify other cells; add a formula or VBA if you want the selection to drive visible output.

To move or resize a control without activating it, right-click it first to select it, then use its handles. If the right-click menu is inconvenient, select the control and use Home > Find & Select > Selection Pane to locate it among worksheet objects.

Understand the Control Settings

The Control tab settings determine how the scroll bar behaves. The minimum and maximum define the endpoints, while the current value is its starting or current position. Keep the current value within the configured limits. A Form Control scroll bar works with whole-number values, so it is not a direct decimal slider.

Minimum and maximum: Set the lowest and highest values the linked cell can receive. For a 20-item list, use 1 and 20.
Incremental change: Sets the amount added or subtracted when the user clicks an end arrow or uses a small-step action. Use 1 when every item should be reachable.
Page change: Sets the larger step used when the user clicks the track on either side of the thumb. A value of 5 makes track clicks move five positions, subject to the control’s limits.
Cell link: Identifies the cell that receives the current value. Keep it visible while configuring the control, then hide the column or style the cell if it should not be prominent.

For a list of 20 records, a useful configuration is minimum 1, maximum 20, incremental change 1, page change 5, and linked cell B2. The linked cell then represents a position, not a worksheet row number. If 1 means the first record in A2:A21, code or a formula must account for that offset.

Use the Linked Value in a Worksheet

A scroll bar becomes useful when its linked value feeds another part of the sheet. One formula-based approach is to use INDEX to return the selected label. With item names in A2:A21 and the scroll bar’s linked value in B2, enter =INDEX(A2:A21,B2) in D2. When B2 is 1, D2 returns the value in A2; when B2 is 20, it returns the value in A21. This assumes B2 stays between 1 and 20.

To display a corresponding value from another column, use the same position with a matching range. With names in A2:A21, prices in C2:C21, and the position in B2, enter =INDEX(C2:C21,B2) in D3. If B2 is 4, D3 returns the fourth price in C2:C21, which is the value in C5. Keeping the source ranges the same height prevents the selected position from pointing to mismatched records.

A scroll bar can also represent a percentage or other scaled input, but its linked cell still holds an integer. For example, set a range from 0 to 100 and display the linked value as a percentage by dividing by 100 in a separate formula. With the slider value in B2, enter =B2/100 in C2 and format C2 as Percentage. A value of 25 in B2 produces 25% in C2. This separates the control’s whole-number steps from the decimal value used in calculations.

Configure a Form Control with VBA

VBA can set a Form Control’s range and linked cell, which is useful when creating or resetting a dashboard. First insert the scroll bar and confirm its worksheet shape name. The example below assumes the control is named Scroll Bar 1, the worksheet is named Dashboard, and B2 is the intended linked cell. It sets a range of 1 to 20, a one-step increment, a five-step page change, and an initial value of 1.

Sub ConfigureScrollBar()
    Dim ws As Worksheet
    Dim bar As Shape

    ' Set up the worksheet and its Form Control scroll bar.
    Set ws = ThisWorkbook.Worksheets("Dashboard")
    Set bar = ws.Shapes("Scroll Bar 1")

    With bar.ControlFormat
        .Min = 1
        .Max = 20
        .SmallChange = 1
        .LargeChange = 5
        .LinkedCell = "B2"
        .Value = 1
    End With
End Sub

To add the code, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. On a Mac, the Visual Basic Editor can be opened with Option+F11 on many keyboards, or through Tools > Macro > Visual Basic Editor; the exact key combination depends on the keyboard and macOS settings. Save a copy of the workbook before running a macro: macro actions cannot be undone with Ctrl+Z. Save macro-enabled workbooks as .xlsm.

The VBA object model exposes a Form Control scroll bar through the worksheet’s Shapes collection and its ControlFormat. If the shape name differs, the sample will not find it; select the control and check its name in the Name Box or Selection Pane, then update the string in the code. VBA can also read its value with ControlFormat.Value. For ordinary worksheet logic, however, using the linked cell directly is often easier to maintain.

Form Control Versus ActiveX and Other Options

Choose a Form Control when the task is simply to let someone select an integer and send that value to a cell. It is quick to configure and can be controlled through ControlFormat in VBA. Choose ActiveX only when you need capabilities specific to that control type and have verified that the workbook will be used in an environment that supports it. ActiveX controls are not supported in Excel for Mac, and may be restricted by security settings.

A data-validation drop-down is often better when users need to see the actual choices, such as product names or department labels. A scroll bar is more compact and works well when choices are ordered and numerous, but it does not show all choices at once. For a small number of options, buttons, a drop-down, or direct cell entry may be clearer and more accessible than a slider.

In Excel for the web, VBA macros cannot be created or run. Workbook controls and their interaction can also differ from desktop Excel, so do not rely on a web user being able to operate a Form Control scroll bar. If the workbook must work in a browser, test the actual file in Excel for the web and consider a cell-based drop-down or another web-compatible input design. Excel for Mac supports VBA, but ActiveX is not available there; Form Control availability and menus can differ from Windows.

Common Problems and Practical Fixes

If the linked cell does not change, reopen Format Control and check the Control tab’s Cell link field. Make sure the link points to the intended cell and that worksheet protection or other workbook restrictions are not preventing interaction. Also confirm that you selected the actual Form Control rather than a decorative shape or a different control.

If the scroll bar moves but the displayed result does not, inspect the formula or macro that uses the linked value. A frequent mismatch is setting the scroll bar to start at 0 while using a formula that expects positions beginning at 1. Another is setting a maximum larger than the number of available records, which can make the lookup formula return an error or an unintended result. Match the minimum and maximum to the number of valid choices.

If VBA reports that it cannot find the control, verify the worksheet tab name and the shape name exactly. A copied control may receive a different name, such as Scroll Bar 2. If a macro is assigned to a control, confirm that the macro is in a standard module and that macros are allowed by the workbook’s security settings. Do not enable macros in files from untrusted sources.

Finally, consider usability. A scroll bar can be difficult to operate with a mouse or touchpad when the control is very short, and it may not communicate the selected item clearly. Display the linked value or selected label nearby, provide a sensible default, and do not make the slider the only way to enter an important value.

Frequently Asked Questions

How do I link an Excel scroll bar to a cell? Right-click the Form Control scroll bar, choose Format Control, open the Control tab, and enter the destination in Cell link. The linked cell will show the scroll bar’s current integer value.

Can a Form Control scroll bar return text? No. Its linked cell supplies a numeric position. Use that number with a lookup formula such as INDEX, or use VBA, to display the corresponding text from a list.

Can I use a scroll bar without VBA? Yes. Link it to a cell and use worksheet formulas to respond to its value. VBA is optional for tasks such as setting up the control programmatically or running a macro when the control is used.

Why does my scroll bar stop before the last item? Check that its maximum matches the number of choices and that its minimum and linked value align with your formula’s indexing. For 20 choices numbered 1 through 20, set the limits to 1 and 20.

Does Excel for the web run a scroll bar’s VBA code? No. Excel for the web does not run VBA macros. If browser use is required, provide an alternative input method and verify that it works in the web version.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.