ScrollBar and SpinButton with Excel VBA

The ScrollBar control is used to set a numeric value, and it can only assign integer non-negative values. The main events of the ScrollBar control are Change, SpinUp, and SpinDown.

Table. ScrollBar Properties

• fmOrientationAuto (orientation depends on control size, default),
• fmOrientationVertical (vertical),
• fmOrientationHorizontal (horizontal) |

The SpinButton control has the same functionality as the ScrollBar. To put it simply, a SpinButton is a scroll bar without a slider.
It has the same properties as the ScrollBar: Value, Min, Max, and SmallChange.

Entering Values into a Cell and Managing Color

As an example of using scroll bars, let’s create an application that demonstrates the RGB color model.

On the worksheet, place three ScrollBar controls.

Using the Properties window, assign them property values as shown in next Table.

Table. Property values set in the Properties window

In the worksheet module (Sheet1), enter the code.

• Each scroll bar can change its value between 0 and 255, with the current value displayed in the linked cell.
• These linked cells work in sync with the scroll bars: if the user changes the value in the cell, the ScrollBar’s Value property updates automatically.
• Each scroll bar controls one of the three RGB color components (Red, Green, Blue).
• Together, they define the background color of the range A2:G5.

Entering Non-Integer Values Using ScrollBar and SpinButton

The Value property of the ScrollBar and SpinButton can only take integer values.
To manage non-integer values, you need to scale them.

We’ll demonstrate this with a project that plots the function:

F(x)=cos⁡(ax)⋅sin⁡(bx)

where the parameters a and b vary from 0 to 10 with a step of 0.1.

Steps:

• On the worksheet, reserve cells B2 and B4 for parameters a and b.
• In the range E2:E12, enter values of x from 0 to 1 with a step of 0.1.
• In cell F2, enter the formula:
• =COS($B$2*E2)*SIN($B$4*E2)
• Select cell F2, drag the fill handle down to F12.
• Based on the range E2:F12, use the chart wizard to create a graph.
• Create two SpinButton controls and, using the Properties window, set their property values as shown in next Table.
• In the worksheet module (Sheet1), enter the code.

Table. Property values set in the Properties window

• The Change event procedures of the SpinButtons input the scaled values into the worksheet cells.
• The worksheet’s Change event procedure ensures synchronization between the cells and the SpinButtons.
  That is, changing the values in B2 or B4 will update the SpinButtons accordingly.