Microsoft Office Tutorials and References
In Depth Information
Referencing UserForm Controls
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
Else
MsgBox “Invalid entry.”, vbCritical
TextBox1.SetFocus
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.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 check 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 conversion ensures that
the comparison won’t generate an error if a value is compared with text. If the SpinButton’s value
doesn’t 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 setup makes it very easy for the user to correct the entry.
Referencing UserForm Controls
When working with controls on a UserForm, the VBA code is usually contained in the code
window for the UserForm. You can also refer to UserForm controls from a general VBA module. To
do so, you need to qualify the reference to the control by specifying the UserForm name. For
example, consider the following procedure, which is located in a VBA module. It simply displays
the UserForm named UserForm1 .
Sub GetData()
UserForm1.Show
End Sub
 
Search JabSto ::




Custom Search