Userform Label Click problem

[shanec]

i have created some dynamic labels on a userform however when i go to link them to with let’s say line1_click it does not recognize it albeit i know that label is definitely there. Is there a setting or something i am missing in the properties.

This is how the label is created

Set newlabel = Statistics_Form3.Controls.Add("Forms.label.1", "line" & labelnumber, True)


thanks for looking at this

Shanec

 

[Colin Legg]

Hi Shane,

Why are you adding the label dynamically at runtime? Could you add it at design time and then just make it visible at runtime if it is needed? That way you can have the event handler code already in situ.

If you add the label dynamically at runtime then you would have to add it's event handler code dynamically at runtime, or you could use a custom class module workaround.

 

[shanec]

Hi Colin thanks so much for answering my message. Given that i have sometimes up to 300 labels on a screen at one time i wrote a routine that adds them dynamically positions them to where i want and fills them from arrayed information, the same routine works for about 30 programs/routines and i just call a simple sub.

Anyway thanks again for pointing me in the right direction how would i add the "event handler code" does that mean code so that the lets say private sub abc_click() macro/proceedure will work.

Thanks again i will start to look for "event handeler" on the web.

 

[Colin Legg]

Hi Shane,

Do all of these 300 labels need click event handlers? If yes, then I think the custom class option would be worth looking at because it means you would only need one communal event handler which can be set up at design time.

Here is an example with 10 labels (please excuse all the magic numbers for the sizing etc.. - the focus is the event handling):

In the userform class module:

Option Explicit
 
Private pLabels As Collection
 
 
Private Sub UserForm_Initialize()
 
    Dim clsLabel As c_Label
    Dim lblNew As MSForms.Label
    Dim i As Long
 
    Set pLabels = New Collection
 
    'size our userform
    Width = 90
    Height = 250
 
 
    'add 10 labels
    For i = 1 To 10
        Set lblNew = Controls.Add( _
                                bstrProgID:="Forms.Label.1", _
                                Name:="Line" & i, _
                                Visible:=True)
 
 
        'position the label etc...
        With lblNew
            With .Font
                .Size = 7
                .Name = "Comic Sans MS"
            End With
 
            .Caption = "Click On Me"
            .Top = i * 20
            .Height = 12
            .Left = 20
            .Width = 50
        End With
 
 
        Set clsLabel = New c_Label
        Set clsLabel.lbl = lblNew
        pLabels.Add clsLabel
    Next i
 
End Sub

Then go to Insert | Class Module to insert a custom class module. In the properties window rename it to c_Label. Then paste in the following code:

Option Explicit
 
Private WithEvents pLabel As MSForms.Label
 
Public Property Set lbl(Value As MSForms.Label)
    Set pLabel = Value
End Property
 
Private Sub pLabel_Click()
    MsgBox "You clicked on label: " & pLabel.Name
End Sub

 

[shanec]

Thanks Colin for pointing me in the right direction. I think i have have made a little progress but have not solved the problem as yet. In answer to your question there are actually only about 6 -8 that need event handlers and it somewhat depends on the information the labels end up with.

I found this, AddHandler Button1.Click, AddressOf Button1_Click on a Microsoft site and am trying to figure how it will work with my lableing creating code above.

I changed it to AddHandler line7.Click, AddressOf line7_Click and get an invalid use of address compile error

As you can guess i am a little new at all of this.

thanks again

 

[Colin Legg]

Hi Shane,

Just a quick heads-up: AddHandler is a VB .Net statement and is not available in Excel VBA, so that's not an option here.

 

[shanec]

so thats why i cant make it work or find any other excel references to it.

 

[Colin Legg]

I'm afraid so - they're quite different languages.

If you're dynamically adding labels and you want event handling then, in VBA, your options are either to dynamically add code to the userform class module or to use a custom class module like in my example. Both of these options are fairly advanced topics so quite often users opt to just add them all at design time and make the ones they need visible at runtime.

What is the purpose of these labels? Would another control such as a listbox be more appropriate?

 

[shanec]

Colin thank you so much the code above works perfectly after integrating it with mine.

a big thank you.....

cheers

Shane

 

[sebamunz]

Hi Shane,

Do all of these 300 labels need click event handlers? If yes, then I think the custom class option would be worth looking at because it means you would only need one communal event handler which can be set up at design time.

Here is an example with 10 labels (please excuse all the magic numbers for the sizing etc.. - the focus is the event handling):

In the userform class module:

Option Explicit
 
Private pLabels As Collection
 
 
Private Sub UserForm_Initialize()
 
    Dim clsLabel As c_Label
    Dim lblNew As MSForms.Label
    Dim i As Long
 
    Set pLabels = New Collection
 
    'size our userform
    Width = 90
    Height = 250
 
 
    'add 10 labels
    For i = 1 To 10
        Set lblNew = Controls.Add( _
                                bstrProgID:="Forms.Label.1", _
                                Name:="Line" & i, _
                                Visible:=True)
 
 
        'position the label etc...
        With lblNew
            With .Font
                .Size = 7
                .Name = "Comic Sans MS"
            End With
 
            .Caption = "Click On Me"
            .Top = i * 20
            .Height = 12
            .Left = 20
            .Width = 50
        End With
 
 
        Set clsLabel = New c_Label
        Set clsLabel.lbl = lblNew
        pLabels.Add clsLabel
    Next i
 
End Sub

Then go to Insert | Class Module to insert a custom class module. In the properties window rename it to c_Label. Then paste in the following code:

Option Explicit
 
Private WithEvents pLabel As MSForms.Label
 
Public Property Set lbl(Value As MSForms.Label)
    Set pLabel = Value
End Property
 
Private Sub pLabel_Click()
    MsgBox "You clicked on label: " & pLabel.Name
End Sub

thanxsssss, eres genial colin. I register only for thanks you. .... mmm post from 2007. Hope you didnt died from covid or something