Pairing a Spin Button with a Text Box

Figure 13-12: This SpinButton is paired with a TextBox.

Figure 13-12: This SpinButton is paired with a TextBox.

on the <d This workbook is available on the companion CD-ROM.

The code required to link a SpinButton with a TextBox is relatively simple. It's basically a matter of writing event handler procedures to ensure that the SpinButton's Value property is in sync with the TextBox's Text property.

The following procedure is executed whenever the SpinButton's Change event is triggered. That is, the procedure is executed when the user clicks the SpinButton or changes its value by pressing the up arrow or down arrow.

Private Sub SpinButtonl_Change()

TextBoxl.Text = SpinButtonl.Value End Sub

The procedure simply assigns the SpinButton's Value to the Text property of the TextBox control. Here, the controls have their default names (SpinButtonl and TextBoxl). If the user enters a value directly into the TextBox, its Change event is triggered, and the following procedure is executed:

Private Sub TextBoxl_Change() NewVal = Val(TextBoxl.Text) If NewVal >= SpinButtonl.Min And _ NewVal <= SpinButtonl.Max Then _ SpinButtonl.Value = NewVal

End Sub

This procedure starts by using VBA's Val function to convert the text in the TextBox to a value. (If the TextBox contains a string, the Val function returns 0.) The next statement determines whether the value is within the proper range for the SpinButton. If so, the SpinButton's Value property is set to the value entered in the TextBox.

The example is set up so that clicking the OK button (which is named OKButton) transfers the SpinButton's value to the active cell. The event handler for this CommandButton's Click event is as follows:

Private Sub OKButton_Click() ' Enter the value into the active cell

If CStr(SpinButtonl.Value) = TextBoxl.Text Then ActiveCell = SpinButtonl.Value Unload Me


MsgBox "Invalid entry.", vbCritical TextBoxl.SetFocus TextBoxl.SelStart = 0 TextBoxl.SelLength = Len(TextBoxl.Text) End If End Sub

This procedure does one final check: It makes sure that the text entered in the TextBox matches the SpinButton's value. This is necessary in the case of an invalid entry. For example, should the user enter 3r into the TextBox, the SpinButton's value would not be changed, and the result placed in the active cell would not be what the user intended. Notice that the SpinButton's Value property is converted to a string that uses the CStr function. This ensures that the comparison will not generate an error if a value is compared with text. If the SpinButton's value does not match the TextBox's contents, a message box is displayed. Notice that the focus is set to the TextBox object, and the contents are selected (by using the SelStart and SelLength properties). This makes it very easy for the user to correct the entry.

About the Tag Property

Every UserForm and control has a Tag property. This property doesn't represent anything specific, and, by default, is empty. You can use the Tag property to store information for your own use.

For example, you might have a series of TextBox controls in a UserForm. The user may be required to enter text into some but not all of them. You can use the Tag property to identify (for your own use) which fields are required. In this case, you can set the Tag property to a string such as Required. Then when you write code to validate the user's entries, you can refer to the Tag property.

The following example is a function that examines all TextBox controls on UserForml and returns the number of required TextBox controls that are empty:

Function EmptyCount() Dim ctl As Control EmptyCount= 0

For Each ctl In UserForml.Controls If TypeName(ctl) = "TextBox" Then If ctl.Tag = "Required" Then If ctl.Text = "" Then

EmptyCount = EmptyCount + 1 End If End If End If Next ctl

End Function

As you work with UserForms, you will probably think of other uses for the Tag property.

+1 0

Post a comment