Excel Made Easy

The VBA scrollbar to set the value in a cell

The Scrollbar is very useful to give the user the possibility to set value in an interactive and graphical way. Here we explain how to use the scrollbar or slider to set a cells value.

In the developer menu, select the scrollbar and place one on the screen where you want it. You can make a vertical one or horizontal one.

VBA scroll bar or slider

Now right click on the bar and set its format. It will open a window allowing various setting to be made like

Minimum and maximum value: sets the minimum and max value the scroll bar can take.

Incremental range: the value that the bar will give you will be multiple of this (for example 5, 10, 15, ...).

Page change: it is the increment of the scroll bar when you click in the scroll bar and not on the arrow.

Cell link: It is the cell that will display the value of the scroll bar.

VBA scroll bar or slider

Now you can see that when you move the scroll bar, the cell that you linked it to, will change according to the value. Isn't this so useful to make a nice user interface looking like a NASA control panel :-)

VBA scroll bar or slider

If you copy and paste you scroll bar (by selecting it, pressing CTRL C and then CTRL V) and then rearranging them, you will have 3 scroll bars like here

VBA scroll bar or slider

You will see that by moving the one scroll bar, all 3 scroll bars will move together. This is because you have to change the linked cell

VBA scroll bar or slider

Now we can do a funny trick is to set the color of one cell just by moving the value of each scroll bar.

Now lets use the scroll bar to

In VBA the color of a cell is set by

Range("cell").Interior.Color = colorofcell

So by selecting our 3 values here we can create any colour existing in the known universe. As you know colors are made of the 3 basics colors RED, GREEN, BLUE. In short RGB. Every color has a value of 0 for dark to 255 to super light (or white in that case)

And to get the final value of the colour you can use

value = RGB(Red, Green, Blue)

So by mixing both the RGB and the Cell color setting function we get the following code.

Sub ScrollBar1_Change()

colorofcell = RGB(Range("e5").value, Range("e7").value, Range("e9").value)

' this is how RGB calculates the colorofcell = Range("f17").value + Range("f20").value * 256 + Range("f23").value * 256 * 256

Range("d7").Interior.Color = colorofcell

End Sub

This is how you can use the scroll bar in a nice manner.

VBA scroll bar or slider

Please Tweet, Like or Share us if you enjoyed.