Userform Controls VBA

[bewsh1987]

Hi All,

Need your help I have 48 Labels on a userform with the name Day1,Day2,Day 3 Ect. Ect. Day47 and Day48

I want to avoid having 48 Sub Procedures example below

Private Sub Day1_Click()
Userform1.Hide
'>>>Call Function and Call Sub Will Go Here<<<
End Sub

Is there a way to have a userform complete a procedure action based on what label was clicked maybe an event handler?

Thank you

Ian

 

[Andrew Poulsom]

I misunderstood what you wanted. If you want one Click event procedure that applies to all your labels you can use a Class Module. Try:

'Class Module named ClassLabels

Public WithEvents LabelGroup As MSForms.Label

Private Sub LabelGroup_Click()
'   Replace the next line with your code
    MsgBox "You clicked " & LabelGroup.Name
End Sub

'UserForm Module

Dim Labels() As New ClassLabels

Private Sub UserForm_Initialize()
    Dim LabelCount As Integer
    Dim Ctrl As Control
    LabelCount = 0
    For Each Ctrl In UserForm1.Controls
        If TypeName(Ctrl) = "Label" Then
            LabelCount = LabelCount + 1
            ReDim Preserve Labels(1 To LabelCount)
            Set Labels(LabelCount).LabelGroup = Ctrl
        End If
    Next Ctrl
End Sub

 

[bewsh1987]

1) I assume that the function is in your Userform CLass Module. Declare it as a Friend routine.

2) Create a class module called clsLabel_Event, and put in the following code

Option Explicit

Private WithEvents ob As MSForms.Label
Private CallBackParent As [userform_name]


Private Sub ob_Click()

    callbackparent.hide
    Call CallBackParent.partNames()

End Sub

Friend Sub WatchControl(oControl As MSForms.Label)

    Set ob = oControl
    Set CallBackParent = ob.parent

End Sub

3)add the following code to your userform class module (I'm free forming this w/o testing, so be sure to debug before running):

option explicit

Private collLabs As Collection

Private Sub UserForm_Initialize()

    dim ctrl as msforms.control
    Dim OBE As clsLabel_Event

    Set collLabs = New Collection

    for each ctrl in me.controls

        if typename(ctrl) = "Label" then

           Set OBE = New clsLabel_Event
        
           collLabs.Add OBE
        
            Call OBE.WatchControl(ctrl)

        end if

    next ctrl

end sub

My function was in the userfrom modules I created a userfrom class module and it worked like a charm

Thanks You so much this has really helped me move forward

 

[bewsh1987]

I misunderstood what you wanted. If you want one Click event procedure that applies to all your labels you can use a Class Module. Try:

'Class Module named ClassLabels

Public WithEvents LabelGroup As MSForms.Label

Private Sub LabelGroup_Click()
'   Replace the next line with your code
    MsgBox "You clicked " & LabelGroup.Name
End Sub

'UserForm Module

Dim Labels() As New ClassLabels

Private Sub UserForm_Initialize()
    Dim LabelCount As Integer
    Dim Ctrl As Control
    LabelCount = 0
    For Each Ctrl In UserForm1.Controls
        If TypeName(Ctrl) = "Label" Then
            LabelCount = LabelCount + 1
            ReDim Preserve Labels(1 To LabelCount)
            Set Labels(LabelCount).LabelGroup = Ctrl
        End If
    Next Ctrl
End Sub

Thank you for this I am still learning and to be honest I've not used class modules in the past and didn't know what potential class modules had, Great code

Thank you