Pairing a Spin Button with a Text Box

A SpinButton has a Value property, but this control doesn't have a caption in which to display its value. In many cases, however, you will want the user to see the SpinButton value. And sometimes you'll want the user to be able to change the SpinButton value directly instead of clicking the SpinButton repeatedly.

The solution is to pair a SpinButton with a TextBox, which enables the user to specify a value either by typing it into the TextBox directly or by clicking the SpinButton to increment or decrement the value in the TextBox.

Figure 13-13 shows a simple example. The SpinButton's Min property is 1, and its Max property is 100. Therefore, clicking the SpinButton's arrows will change its value to an integer between 1 and 100.

SpinButton / TextBox Demo

Specify 5 value befr een 1 and 100:

| 3: jj

! OK

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

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

CD- This workbook is available on the companion CD-ROM. The file is named spinbutton ROM and textbox. xlsm.

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 always 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 SpinButton1_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 non-numeric text, 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(SpinButton1.Value) = TextBox1.Text Then ActiveCell = SpinButton1.Value Unload Me


MsgBox "Invalid entry.", vbCritical TextBox1.SetFocus TextBox1.SelStart = 0

TextBox1.SelLength = Len(TextBox1.Text) End If End Sub

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 UserForm1 and returns the number of required TextBox controls that are empty:

Function EmptyCount() Dim ctl As Control EmptyCount= 0

For Each ctl In UserForm1.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._

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, if the user enters 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 by using 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.



Was this article helpful?

+1 -5


  • faruz
    How to sync spinbutton with textbox vba?
    6 years ago
  • oliwia
    How to connect spin button to textbox vba?
    3 years ago
  • Melba Brandagamba
    How to link a textbox to spin button?
    1 year ago
  • antonietta
    How to connect spin button with radiobuttons?
    6 months ago
  • adamo
    How to link a spinbutton to a textbox in vba?
    3 months ago
  • anthony
    How to link a spin button to a text box userform?
    3 months ago
  • Scott
    What is a spin button on userform word?
    1 month ago

Post a comment