VBA to capture active userform label caption

[Trebor76]

Hi there,

I've been struggling with this for hours, which (in my mind anyway), should be an easy one.

All I want to do is set a string variable with the active label’s (i.e. just clicked) userform caption. The label is a form control not an ActiveX control.

Hope someone can help.

Regards,

Robert

 

[Trebor76]

Sure. It's for a little Keno game I'm developing. Each number (1 to 80) is a small label that once clicked changes appearance as a way to show the user it's been selected and then for it to be checked against as the Keno numbers are drawn.

This is done via one formatting macro that needs the label name to run or else I'd have to have the same code 80 times - unless there's a more efficient way that I'm missing (very possible).

 

[RoryA]

Sounds to me like you want a class module. That way you put the click event code in once and hook it up to every label on the form.

There's an example here (though not for labels specifically) and various others on the forum.

 

[Trebor76]

Thanks Rory.

I did see very similar code to the link you provided by John Walkenbach but thought it seemed like overkill to do a seemingly simple task.

I think I'll just go down the path of hard coding the label name when I call the formatting macro.

I really appreciate your (and everyone else's) help with this.

 

[RoryA]

OK. For 80 labels I'd use a class - really not a lot to it in code terms. If you are interested, I can put together a quick sample of what you'd need?

 

[rohitbaby]

Hi,

Try this code:

Private Sub lbl04_Click()
Dim strMyName As String

strMyName = lbl04.Name

End Sub

 

[Trebor76]

Hi rohitbaby,

Thanks for this - but this hard coded method is almost the same that I'm using. I want the code to be dynamic something like ActiveLabel.Name though as Rory has pointed out no such VBA method exists for a label.

Hi Rory,

WOW - that is so kind!! If you could do up some code to only work on those labels that use the naming convention of "lblnn" (where nn is the number from 01 to 80) it would be greatly appreciated. I'll make sure to mention you and this thread in the form's Visual Basic Editor

Kind regards,

Robert

 

[RoryA]

Here goes:

Class module named CLabelHandler:

Option Explicit

Public WithEvents lbl As MSForms.Label

Private Sub lbl_Click()
   ' just demo code - adjust to your real needs!
   MsgBox lbl.Caption
End Sub

Userform code:

Option Explicit
' collection to store instances of label handler class
Dim colLabels As Collection

Private Sub UserForm_Initialize()
   Dim objLblHandler As CLabelHandler
   Dim ctl As Control
   Set colLabels = New Collection
   ' loop through controls
   For Each ctl In Me.Controls
      ' test if label and if name matches
      If TypeOf ctl Is MSForms.Label Then
         If LCase(ctl.Name) Like "lbl##" Then
            ' create new instance of class
            Set objLblHandler = New CLabelHandler
            ' assign label to its variable
            Set objLblHandler.lbl = ctl
            ' add instance to collection so it stays in scope
            colLabels.Add objLblHandler
         End If
      End If
   Next ctl
End Sub

I've added comments so hopefully it makes sense. (it would be better to make the lbl variable a private member of the class and access it through properties, but I'm keeping it simple here! )

 

[Trebor76]

Thanks Rory, but tell me - how do I call the class module to run when I click on one of my existing labels?

 

[RoryA]

That's what the userform_Initialize sub does. You don't need to do anything else. (if you implement that code and then click any of the labels on your form, you'll see what I mean.)

 

[Trebor76]

Thanks Rory - nearly there (it's getting late here in Oz). I tried running the following in the class module but it returned 'Sub or Function not defined' due the 'Option Explicit' statement. How do I achieve the following whenever I click on the desired label?

If lbl.Caption < 10 Then
        Call MySelection("lbl0" & lbl.Caption)
    Else
        Call MySelection("lbl" & lbl.Caption)
    End If

Robert