Microsoft Office Tutorials and References
In Depth Information
Understanding UserForm Events
You might think that you could disable events by setting the EnableEvents property of the
Application object to False . Unfortunately, this property applies only to events that involve
true Excel objects: Workbooks , Worksheets , and Charts .
Pairing a SpinButton with a TextBox
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’ll 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
Figure 13-13: This SpinButton is paired with a TextBox.
This workbook is available on the companion CD-ROM. The file is named spinbutton
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()
TextBox1.Text = SpinButton1.Value
The procedure simply assigns the SpinButton’s Value to the Text property of the TextBox
control. Here, the controls have their default names ( SpinButton1 and TextBox1 ). If the user
enters a value directly into the TextBox, its Change event is triggered, and the following
procedure is executed: