Create a Class Module for MouseMove

Question

Ok, after messing with for awhile I got the module to work using the the .Tag property and it works great. The label boxes I want to change I use a "?" in its tag and it doesn't matter what the name is and the Image I use a "*" in its .Tag property for the border to work. 

My question is, right now in my forms module I'm using a lot of MouseMoves for these controls, can I create a Class Module that can be used for the MouseMove of these Labels and Images and a Userform Mousemove for restore? I tried messing with but I'm not knowing where to start. Below is what I have. The module works fine its the Class that doesnt.

Standard Module......

Option Explicit Sub ControlColorChange(ctrl As Control)

If TypeName(ctrl) = "Label" And ctrl.Tag = "?" Then

ctrl.ForeColor = &H80FF&

ctrl.Font.Size = 9

ctrl.Font.Bold = True

End If

If TypeName(ctrl) = "Image" And ctrl.Tag = "*" Then

ctrl.BorderColor = &H80FF&

End If

End sub

RestoreControlColours(UsForm As UserForm)

Dim ctrl As Control

For Each ctrl In UsForm.Controls

If TypeName(ctrl) = "Label" And ctrl.Tag = "?" Then

ctrl.ForeColor = &HA76C42

ctrl.Font.Size = 8

ctrl.Font.Bold = False

End If

If TypeName(ctrl) = "Image" And ctrl.Tag = "*" Then

ctrl.BorderColor = &HF5EFEA

End If

Next ctrl

End Sub

Class Module (Doesn't Work)

Option Explicit

Public WithEvents ALabel As MSForms.label

Public WithEvents BImage As MSForms.Image

Private Sub ALabel_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

ControlColorChange ALabel

End Sub

Private Sub BImage_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

ControlColorChange BImage

End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) RestoreControlColours Me End Sub

Answer 1

Please unmark my previous reply - it doesn't solve your problem. Perhaps this one will.

Class module:

Option Explicit

Public WithEvents ALabel As MSForms.Label

Public WithEvents BImage As MSForms.Image

Public WithEvents UForm As MSForms.UserForm

Private Sub ALabel_MouseMove(ByVal Button As Integer, _

        ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    ControlColorChange ALabel

End Sub

Private Sub BImage_MouseMove(ByVal Button As Integer, _

        ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    ControlColorChange BImage

End Sub

Private Sub UForm_MouseMove(ByVal Button As Integer, _

        ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    RestoreControlColours UForm

End Sub

Standard module:

Option Explicit

Public cls() As New clsEvent ' Replace with the name of your class module

Sub ControlColorChange(ctrl As MSForms.Control)

    If TypeName(ctrl) = "Label" And ctrl.Tag = "?" Then

        ctrl.ForeColor = &H80FF&

        ctrl.Font.Size = 9

        ctrl.Font.Bold = True

    End If

    If TypeName(ctrl) = "Image" And ctrl.Tag = "*" Then

        ctrl.BorderColor = &H80FF&

    End If

End Sub

Sub RestoreControlColours(UsForm As MSForms.UserForm)

    Dim ctrl As MSForms.Control

    For Each ctrl In UsForm.Controls

        If TypeName(ctrl) = "Label" And ctrl.Tag = "?" Then

            ctrl.ForeColor = &HA76C42

            ctrl.Font.Size = 8

            ctrl.Font.Bold = False

        End If

        If TypeName(ctrl) = "Image" And ctrl.Tag = "*" Then

            ctrl.BorderColor = &HF5EFEA

        End If

    Next ctrl

End Sub

Sub ShowForm()

    Dim n As Long

    Dim ctrl As MSForms.Control

    For Each ctrl In UserForm1.Controls

        If TypeName(ctrl) = "Label" And ctrl.Tag = "?" Then

            n = n + 1

            ReDim Preserve cls(1 To n)

            Set cls(n).ALabel = ctrl

        End If

        If TypeName(ctrl) = "Image" And ctrl.Tag = "*" Then

            n = n + 1

            ReDim Preserve cls(1 To n)

            Set cls(n).BImage = ctrl

        End If

    Next ctrl

    n = n + 1

    ReDim Preserve cls(1 To n)

    Set cls(n).UForm = UserForm1

    UserForm1.Show

End Sub

Change clsEvents to the name of the class module and UserForm1 to the name of the userform.

Answer 2

A class module in itself doesn't do anything; you have to create an instance for each use.

In the ShowForm macro, we build an array of instances of the class module, one for each label and image with the correct tag, and one instance for the userform as a whole. We assign the appropriate variable for each instance ALabel for the labels, BImage for the image controls and UForm for the userform. This assignment is what makes the controls and userforms use the MouseMove event procedures from the class module.

I got the basic idea from John Walkenbach's tip http://www.j-walk.com/ss/excel/tips/tip44.htm.

Answer 3

Thanks Hans that was it. Can you explain what the added code is doing? Thanks!

Answer 4

Run the ShowForm macro to display the userform.