Originally Posted by
dj44
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
Reply With Quote
