Using a Spin Button and a Text Box

A SpinButton control lets the user specify a number by clicking arrows. This control consists only of arrows (no text), so you usually want a method to display the selected number. One option is to use a Label control, but this has a disadvantage: The user can't type text in a Label. A better choice is to use a TextBox.

A SpinButton control and TextBox control form a natural pair. Excel uses them frequently. (Check out the Print dialog box for a few examples.) Ideally, the SpinButton and its TextBox should be in sync: If the user clicks the SpinButton, the SpinButton's value should appear in the TextBox. And if the user enters a value directly into the TextBox, the SpinButton should take on that value. Figure 18-8 shows a custom dialog box with a SpinButton and a TextBox.

Figure 18-8:

A UserForm with a SpinButton and a companion TextBox.

This UserForm contains the following controls:

1 A SpinButton named SpinButton1, with its Min property set to 1 and its Max property set to 100

1 A TextBox named TextBox1

1 A CommandButton named OKButton

The event-handler procedure for the SpinButton follows. This procedure handles the Change event, which is triggered whenever the SpinButton value is changed. When the SpinButton's value changes (when it's clicked), this procedure assigns the SpinButton's value to the TextBox. To create this procedure, double-click the SpinButton to activate the Code window for the UserForm.

Private Sub SpinButton1_Change()

TextBox1.Text = SpinButton1.Value End Sub

The event-handler for the TextBox, which is listed next, is a bit more complicated. To create this procedure, double-click the TextBox to activate the Code window for the UserForm. This procedure is executed whenever the user changes the text in the TextBox.

Private Sub TextBox1_Change() Dim NewVal As Integer

NewVal = Val(TextBox1.Text) If NewVal >= SpinButton1.Min And _ NewVal <= SpinButton1.Max Then _ SpinButton1.Value = NewVal

End Sub

Figure 18-8:

A UserForm with a SpinButton and a companion TextBox.

This procedure uses a variable, which stores the text in the TextBox (converted to a value with the Val function). It then checks to ensure that the value is within the proper range. If so, the SpinButton is set to the value in the TextBox. The net effect is that the SpinButton's value is always equal to the value in the TextBox (assuming that the SpinButton's value is in the proper range).

This example is available at this book's Web site. It also has a few other bells and whistles that you may find useful.

0 0

Post a comment