Using a Scrollbar as a Slider to Select Values

Chapter 9 discussed using a spin button control to allow someone to choose a date. The spin button is good, but it allows clients to adjust up or down by only one unit at a time. An alternative method is to draw a horizontal scrollbar in the middle of the userform and use it as a slider. Clients can use arrows on the ends of the scrollbar like the spin button arrows, but they can also grab the scrollbar and instantly drag it to a certain value.

The userform shown in Figure 21.12 includes a label named Label1 and a scrollbar called ScrollBar1. The userform's Initialize button sets up the Min and Max values for the scrollbar. It initializes the scrollbar to a value from cell A1 and updates the Labell.Caption:

Private Sub UserForm_Initialize() Me.ScrollBar1.Min = 0 Me.ScrollBar1.Max = 100 Me.ScrollBar1.Value = Range("A1").Value Me.Label1.Caption = Me.ScrollBar1.Value End Sub

Two event handlers are needed for the scrollbar. The _Change event handles if clients click on the arrows at the ends of the scrollbar. The _Scroll event handles if they drag the slider to a new value:

Private Sub ScrollBar1_Change()

1 This event handles if they touch ' the arrows on the end of the scrollbar Me.Label1.Caption = Me.ScrollBar1.Value End Sub

Private Sub ScrollBar1_Scroll()

' This event handles if they drag the slider Me.Label1.Caption = Me.ScrollBar1.Value End Sub

Finally, the event attached to the button writes the scrollbar value out to the worksheet:

Private Sub CommandButton1_Click()

Range("A1").Value = Me.ScrollBar1.Value Unload Me End Sub

The scrollbar is shown in action in Figures 21.11 and 21.12. Figure 21.11

Figure 21.12

Using a scrollbar control as a control allows the user to quickly drag to a particular numeric or data value.

Figure 21.12

Using a scrollbar control as a control allows the user to quickly drag to a particular numeric or data value.

Access Userform
+2 0

Responses

Post a comment