Userform Label Class - Reference Worksheet Cells Range - Run Macros Event Click

[GTO]

Hi folks,

...
I have become stuck now as Im not sure - how to initilaise this, its not working.

I also need to put something in the userform initialise?

Hi DJ,

Depending on your project, I'd likely agree with Sam, but if there is an advantage to using a Class for the controls' click event, then I think you missed a step. You are Dim'ming aLabel in the Initialize Event as an individual variable (Scalar), rather than building a collection or array of some type to hold all the instances of the Class. Does that make sense?

If you look at Mike's example again, you will see he declared a New Collection at the top of the form's module. (Download Ken's example at #6 of your link). I tend to avoid auto-instancing, but regardless of that, here's an example using an array (Similar results; you just need something to keep all the created instances in memory while you are using them.).

On the "Macro" worksheet (A1:B21) :

Label Name	Procedure Name
myLabel01	MyMacro_01
myLabel02	MyMacro_02
myLabel03	MyMacro_03
myLabel04	MyMacro_04
myLabel05	MyMacro_05
myLabel06	MyMacro_06
myLabel07	MyMacro_07
myLabel08	MyMacro_08
myLabel09	MyMacro_09
myLabel10	MyMacro_10
myLabel11	MyMacro_11
myLabel12	MyMacro_12
myLabel13	MyMacro_13
myLabel14	MyMacro_14
myLabel15	MyMacro_15
myLabel16	MyMacro_16
myLabel17	MyMacro_17
myLabel18	MyMacro_18
myLabel19	MyMacro_19
myLabel20	MyMacro_20

In a Class named 'clsLabel' :

Option Explicit
  
Private OwnerForm As Object
Private WithEvents OneLabel As MSForms.Label
  
Public Property Set ParentForm(form As Object)
  Set OwnerForm = form
End Property
  
Public Property Set ThisLabel(labl As MSForms.Label)
  Set OneLabel = labl
End Property
  Public Property Get ThisLabel() As MSForms.Label
    Set ThisLabel = OneLabel
  End Property
  
Private Sub OneLabel_Click()
Dim WS As Worksheet
Dim Index As Long
  
  '// I would use  the sheet's CodeName, but either ways, test to make sure it's there.//
  On Error Resume Next
  Set WS = ThisWorkbook.Worksheets("Macro")
  On Error GoTo 0
  
  If WS Is Nothing Then
    OwnerForm.Hide
    DoEvents
    MsgBox "Someone changed the sheet name", vbCritical, vbNullString
    Unload OwnerForm
    Exit Sub
  End If
  
  On Error Resume Next
  Index = Application.Match(ThisLabel.Name, WS.Range("A2:A21"), 0)
  On Error GoTo 0
  
  If Index > 0 Then
    OwnerForm.Hide
    DoEvents
    Application.Run "basMain." & CStr(WS.Cells(Index + 1, 2).Value), ThisLabel.Name
    OwnerForm.Show
  Else
    OwnerForm.Hide
    DoEvents
    MsgBox "I couldn't find ThisLabel.Name", vbCritical, vbNullString
    Unload OwnerForm
  End If
  
End Sub

In a Standard Module named 'basMain' :

Option Explicit
  
Public Sub MyMacro_01(ByVal msg As String)
  MsgBox "I was called from """ & msg & """.", vbInformation, vbNullString
End Sub
  
Public Sub MyMacro_02(ByVal msg As String)
  MsgBox "I was called from """ & msg & """.", vbInformation, vbNullString
End Sub
  
Public Sub MyMacro_03(ByVal msg As String)
  MsgBox "I was called from """ & msg & """.", vbInformation, vbNullString
End Sub
  

'<---repeated thru --->

  
Public Sub MyMacro_20(ByVal msg As String)
  MsgBox "I was called from """ & msg & """.", vbInformation, vbNullString
End Sub

In a UserForm named 'formTester' :

Option Explicit
  
Dim cLabels(1 To 20) As clsLabel
  
Private Sub cmdUnload_Click()
  Unload Me
End Sub
  
Private Sub UserForm_Initialize()
Dim n As Long
  
  For n = 1 To 20
    Set cLabels(n) = New clsLabel
    With cLabels(n)
      Set .ParentForm = Me
      Set .ThisLabel = Me.Controls("myLabel" & Format(n, "00"))
    End With
  Next
    
End Sub

This assumes 20 labels named as shown; see attached example file.

Hope that helps,

Mark