Microsoft Office Tutorials and References
In Depth Information
The Control Toolbox
Draw a Label control and four CheckBoxes from the Control
Toolbox below the CommandButton. In Figure 13-14, I changed
the Label’s caption to Check Your Favorite Activities. I changed
each CheckBox’s caption to a different leisure activity.
Either double-click the CommandButton, or right-click it and
select View Code. Either way, you’ll be taken to the worksheet
module and the default Click event will be started for you with
the following entry:
Private Sub CommandButton1_Click()
End Sub
figurE 13-14
VBA code for embedded ActiveX objects is almost always in the module of the
worksheet upon which the objects are embedded.
For this demonstration, when the CommandButton is clicked, it will evaluate every embedded
object on the worksheet. When the code comes across an ActiveX CheckBox, it will determine
whether the CheckBox is checked. At the end of the procedure, a Message Box will appear,
confirming how many (if any) CheckBoxes were checked, and their captions. The entire code will
look as follows.
Private Sub CommandButton1_Click()
‘Evaluate which checkboxes are checked.
‘Declare an Integer type variable to help
‘count through the CheckBoxes, and an Object
‘type variable to identify the kind of ActiveX control
‘(checkboxes in this example) that are selected.
Dim intCounter As Integer, xObj As OLEObject
‘Declare a String variable to list the captions
‘of the selected checkboxes in a message box.
Dim strObj As String
‘Start the Integer and String variables.
intCounter = 0
strObj = “”
For Each xObj In ActiveSheet.OLEObjects
If TypeName(xObj.Object) = “CheckBox” Then
If xObj.Object.Value = True Then
intCounter = intCounter + 1
strObj = strObj & xObj.Object.Caption & Chr(10)
End If
Search JabSto ::

Custom Search