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

Hi folks,

needing some help from the vba pros :)

I have created a userform.

I have put a lot of labels on it. I would like to run a macro when each label is clicked.

I have put the macro names in a worksheet called "Macros" in excel.

Column A (LabelName) | Column B (Macro To Call)
Label1============== Macro_That
Label2 ==============Macro_This
Label3 ==============Macro_Data
......
and more

I started with a label class so this is from here:

http://www.vbaexpress.com/forum/show...lated-userform

Code:

---


Option Explicit
'mikerickson, http://www.vbaexpress.com/forum/showthread.php?t=36832
Public WithEvents AnyLabel As MSForms.Label
 
Private Sub AnyLabel_Click()


Dim i                        As Integer
Dim oLabel            As Control
Dim ows                  As Worksheet

    Set ows = Worksheets("Macro")
    For i = 2 To 20

    If oLabel.Name = ows.Cells(i, A).Value Then

    Application.Run ows.Cells(i, B).Value
   
End If
Next i


    'With AnyLabel
    'MsgBox "You clicked on " & .Parent.Name & "." & .Name
    'End With


End Sub

---

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?
I wont post my more of my feeble attempt as it may confuse more than help

Code:

---

sub userform_initialise()
Dim aLabel As clsAnyLabel
    Dim oneControl As Object
    For Each oneControl In Me.Controls
        If TypeName(oneControl) = "Label" Then
            Set aLabel = New clsAnyLabel
            Set aLabel.AnyLabel = oneControl
  .......?
end sub

---

if any one can assist me i would be really grateful, as ive been around many places to fix this and i better ask the experts on these experienced matters

thank you very much for your kind help

dj

That is a lot of work just to duplicate built-in functionality.

Note this emboldened quote from that link

Quote:

---

I have created a userform that is programmatically populated with labels

---

But it sounds like you inserted the Label Controls at Design time. Labels are clickable like any other Control

Code:

---

Private Sub Label1_Click()
'Write your "macro" code here.
End Sub

---

Hi Sam,
thanks for tip.

I know that you can repeat the label click

sub label1_click()
call macro1
end sub

sub label2_click()
call macro2
end sub

but because there are so many, I wanted to make it simpler for me. So I put it in a column - so i can update it easier.

Because the userform code is become very bloated, with lots of other bits and bobs.

Can I not call the worksheet range from a class?

I know a list box can populate from xl

i appreciate the ideas
dj

Quote:

---

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' :

Code:

---

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' :

Code:

---

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' :

Code:

---

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

Hi Mark,

- just to say that you folk are over and beyond kind and generous with your coding help, I really do appreciate the help and skill it requires to code,
as I get stuck and spend days in a coding stupour,

thank you for your generosity mark, let me go away and test the fine code
dj

Hi Mark,

I am so happy it is working a treat in excel, exactly how I imagined it but could not explain it succinctly. :grinhalo:

I was careful not to mess about to much with it in case I broke it.

I can put all my macros in any module, and then just update my macro list sheet and hey presto,

That saves me a lot of time and hassle.

I have a lot of mini macros and well they are a real headache because it would take me ages to find and edit them, and i would also change the name and then it would get lost - so that was a no no, I have to keep organised as I'm a hopeless newbie coder

The original task is complete. :checkmark

Now since it worked so well in excel, may I trouble you just a little -

Would this work in Word? I could make a list of macros names in an excel sheet and just reference it.

I would just use the excel sheet to organise my macros.

The word macros would stay in Word.

If its not too much of a headache - would you be good enough to have a look at the word form.

I transferred the excel modules and classes and form to word. ( Hope i didnt make a dogs dinner of it)

I didnt want to start a new thread as I'm not sure if its possible, and if not I am happy with my Excel Version

thank you again and grateful for your advice and help

dj

Quote:

---

but because there are so many, I wanted to make it simpler for me. So I put it in a column - so i can update it easier.

Because the userform code is become very bloated, with lots of other bits and bobs.

---

Sounds like a nightmare of a UI.

All the 'called' "Macros" can be in a different Module. Only the Event Subs have to be in the UserForm Code Module.

In this example,changing a Label's Caption changes the Macro that is called.

UserForm Code

Code:

---

Private Sub Label1_Click()
RunProcedure Me.Label1.Caption 'Label1 Caption = "Artists"
End Sub

---

Module 'modLabelCodes' Code

Code:

---

Public Sub RunProcedure(MacroName As String)
CallByName MacroName
End Sub

---

Code:

---

Private Sub Artists()
blah bleh bloh
End Sub

---

It is my practice to choose one base name for all related Controls and subs.

For example a Label, with Caption "ArtistName:" is adjacent to a TextBox named "tbxArtistName, which calls a sub named "GetArtistName," which uses a Range, Collection, Array, or Dictionary named "rng," "col," "arr," or dic" & "ArtistNames" ( "rngArtistNames.") In this example, since the Label is only for UI Information and is not clickable, it is not named other than the default auto-name.

I also keep all such name Prefixes to 3 characters. This allows me to use such code compression tricks as

Code:

---

Private Sub cbutSaveData_Click()
For each Ctrl in Me.Controls
If Ctrl.Tag = "IO" Then _
Range("rng" & Mid(Ctrl.Name, 4)).Cells(1).End(xlDown).Offset(1) = Ctrl.Value
Next
End Sub

---

Hi Sam,

sometimes to make it simple it does become complex.

It's working nicely with the XL, i'll keep it at that.

thanks again all

DJ