Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform Label | Excel Discussion (Misc queries) | |||
userform label double-click goes to click event | Excel Programming | |||
Flashing UserForm Label | Excel Programming | |||
Userform blinking label | Excel Programming | |||
UserForm label doesn't load? | Excel Programming |