Userform Label Click

[Zone]

I have 30 labels on my userform. For each label that's clicked, I
want to remember the name of the label and then run a subroutine. All
of them run the same sub, and that sub does different things depending
on the name of the label. Do I have to have to have 30 different
click event subs for the 30 different labels?
TIA, James

 

[Susan]

if you want each click to do something different, then yes.

if you want them to do all the basic same thing, then yes, but smaller
- you could have it coded like this:

sub label1_click()
myVariable = label1.caption
call Heres_The_Real_Work
end sub

sub label2_click()
myVariable = label2.caption
call Heres_The_Real_Work
end sub

sub label3_click()
myVariable = label3.caption
call Heres_The_Real_Work
end sub


then have one sub

sub Heres_The_Real_Work()
'whatever you want it to do each time a label is clicked
end sub

hth
susan

 

[Zone]

Thanks, Susan. I suspected that would be the case. I already coded
it, very similar to your example. But I thought maybe there was a
simpler approach. Regards, James

 

[Chip Pearson]

You can do this very easily with a Class and a Collection. Insert a new
Class Module (from the Insert menu in the VBA editor) and give it a name of
"CLabelClass". Paste the following code in the class module:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit

Public WithEvents FrmLabel As MSForms.Label

Private Sub FrmLabel_Click()
MsgBox "You clicked Label with caption: " & FrmLabel.Caption
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Then in your user form, insert the following code:

Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Coll As New Collection

Private Sub UserForm_Initialize()
Dim Ctrl As MSForms.Control
Dim CLabel As CLabelClass

For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.Label Then
Set CLabel = New CLabelClass
Set CLabel.FrmLabel = Ctrl
Coll.Add CLabel
End If
Next Ctrl
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Now when your user form starts up, it will create a new instance of
CLabelClass for each label control on your form and set that instance's
FrmLabel object to the Label control. When the user clicks on a label on the
form, the _Click event of the appropriate instance will be triggered. Put
your code in that Click event. Thus, you'll only write code once, but N
"copies" of the code, one for each Label, will exist in memory.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[shah shailesh]

See below site of John titled
Handle Multiple UserForm Buttons With One Subroutine

http://www.j-walk.com/ss/excel/tips/tip44.htm

Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)
If You Can't Excel with Talent, Triumph with Effort.

 

[Susan]

thanks, Chip, i didn't know about using a class module.......

susan