Microsoft Office Tutorials and References
In Depth Information
Class Modules for Embedded Objects
You can also tap into other events in the same class module. All that’s required is that you use the
same class object ( cmdButtonGroup in this example), and that the event is supported by the object.
With CommandButtons, the MouseOver event can help you identify which button you are hovering
your mouse over by shading it orange, while all other CommandButtons on the sheet are colored gray.
I used hex codes in this example for the buttons’ BackColor property, to show
how you’d use hex in code to refer to colors. These hex values are always shown
in the Properties window of ActiveX controls for BackColor and ForeColor
properties, and I personally find them very reliable in VBA code
with any version of Excel.
Private Sub cmdButtonGroup_MouseMove _
(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
Dim myBtn As Object
For Each myBtn In ActiveSheet.OLEObjects
If TypeName(myBtn.Object) = “CommandButton” Then _
myBtn.Object.BackColor = &HC0C0C0 ‘turn all to gray
cmdButtonGroup.BackColor = &H80FF& ‘orange
End Sub
As you can probably tell, despite the appearance of differently shaped
CommandButtons with comical captions, the larger point of this example
is that you can capture various properties of class objects, assign them to a
variable, and utilize that variable information in other macros, or even as
part of the class module’s event code. For example, in real practice, you
don’t need or want a Message Box to pop up and tell you which button
you just clicked; you already know that. If, for example, your project is
such that the CommandButtons’ captions have a word or phrase to be used
as a criterion for automatically filtering a table of data, this application of
flexible class module coding will save you a lot of work.
For embedded ActiveX controls, you can instantiate the collection of OLE objects, in this example
for CommandButtons, with the following code that goes into the ThisWorkbook module. Be sure to
place this example declaration statement at the top of the ThisWorkbook module:
Dim cmdButtonHandler() As New Class1
Search JabSto ::

Custom Search