Microsoft Office Tutorials and References
In Depth Information
Class Modules for Embedded Objects
Finally, utilize the Open event to collect the CommandButtons that are only on Sheet1. Notice the
references to the OLEObject and OLEObjects keywords when dealing with embedded ActiveX controls.
Private Sub Workbook_Open()
Dim cmdButtonQuantity As Integer, MYcmdButton As OLEObject
cmdButtonQuantity = 0
With ThisWorkbook
For Each MYcmdButton In .Worksheets(“Sheet1”).OLEObjects
If TypeName(MYcmdButton.Object) = “CommandButton” Then
cmdButtonQuantity = cmdButtonQuantity + 1
ReDim Preserve cmdButtonHandler(1 To cmdButtonQuantity)
Set cmdButtonHandler(cmdButtonQuantity).cmdButtonGroup _
= MYcmdButton.Object
End If
Next MYcmdButton
End With
End Sub
Not all controls recognize the same event types though,
so you’d need to set a class event that the object type can
recognize.
There is another technique using the Collection keyword
for grouping the same types of objects into a class. In this
example, Sheet1 has a number of embedded CheckBox
controls, and you want to write one small piece of VBA
code that will apply to all CheckBoxes.
The visual effect you want is for any CheckBox on
Sheet1 to be shaded black if it is checked, and white if it
is unchecked. Figure 21-7 shows the differences in color
shading depending on the status of the CheckBoxes.
The code to do this is surprisingly minimal. Insert a new
class module, and assuming it is named Class2 because
you already have a Class1 module established, this code
will go into the Class2 module:
Public WithEvents grpCBX As MSForms.CheckBox
figurE 21-7
Private Sub grpCBX_Click()
With grpCBX
If .Value = True Then
.BackColor = &H0& ‘Black background
.ForeColor = &HFFFFFF ‘While font
Else
.BackColor = &HFFFFFF ‘White background
.ForeColor = &H0& ‘Black font
End If
End With
End Sub
Search JabSto ::




Custom Search