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

[dj44]

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/showthread.php?36832-Creating-an-OnClick-event-for-an-autopopulated-userform




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



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

[SamT]

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

Note this emboldened quote from that link
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

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

[dj44]

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

[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

[dj44]

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

[dj44]

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

[SamT]

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

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

Module 'modLabelCodes' Code

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

Private Sub Artists()
blah bleh bloh
End Sub

[SamT]

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

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

[dj44]

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