Userform Label Click
 

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

Userform Label Click
 

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


On Jun 25, 12:03 pm, Zone wrote:
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

Userform Label Click
 

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

On Jun 25, 12:12?pm, Susan wrote:
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

On Jun 25, 12:03 pm, Zone wrote:



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- Hide quoted text -

- Show quoted text -

Userform Label Click
 

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)



"Zone" wrote in message
oups.com...
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

On Jun 25, 12:12?pm, Susan wrote:
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

On Jun 25, 12:03 pm, Zone wrote:



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- Hide quoted text -

- Show quoted text -

Userform Label Click
 

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.


"Zone" wrote in message
oups.com...
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

Userform Label Click
 

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


On Jun 25, 12:39 pm, "Chip Pearson" wrote:
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 Consultingwww.cpearson.com
(email on the web site)

"Zone" wrote in message

oups.com...



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

On Jun 25, 12:12?pm, Susan wrote:
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

On Jun 25, 12:03 pm, Zone wrote:

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -