RaiseEvent is fired only for the last control

PetLahev · Aug 14, 2011

Hello

I've been trying to solve this problem for hours with no success.
Please, kick me
It should be simple
1, form with labels that are added dynamicaly
2, a common class that catch labels event

I prepared a sample with one form and one class

code in the form - UserForm1

Code:

Dim storeInstances          As Collection
Dim WithEvents lblEvents    As LablesClass

Private Sub lblEvents_Clicked(labelName As String)
        MsgBox "Whooo the raise event works for " & labelName
End Sub

Private Sub UserForm_Initialize()
    
    Set storeInstances = New Collection
    
    Set lblEvents = New LablesClass
    Set lblEvents.lablesEvent = Label1
    storeInstances.Add lblEvents
    
    Set lblEvents = New LablesClass
    Set lblEvents.lablesEvent = Label2
    storeInstances.Add lblEvents
    
    Set lblEvents = New LablesClass
    Set lblEvents.lablesEvent = Label3
    storeInstances.Add lblEvents
    
End Sub

code in the class - LablesClass

Code:

Public WithEvents lablesEvent As MSForms.Label

Public Event Clicked(labelName As String)

Private Sub lablesEvent_Click()
    MsgBox lablesEvent.Name
    RaiseEvent Clicked(lablesEvent.Name)
End Sub

I'd like to achieve a result when every click on any label will raise the RaiseEvent code.

Thanks
Pat

mikerickson · Aug 14, 2011

I'm not sure why the RaiseEvents doesn't work, but this is how I would give a userform's labels the same event code. The difference between this approach and the one above is that in this approach the common code is in the Class module not in the userforms module

Code:

Rem class module "clsCommonLabel"

Public WithEvents myLabel As MSForms.Label

Private Sub myLabel_Click()
    MsgBox "You clicked on " & myLabel.Name
End Sub

Code:

Rem code in userform module

Dim CommonCodeLabels As Collection

Private Sub UserForm_Initialize()
    Dim oneLabel As Variant
    
    Dim oneCommonLabel As clsCommonLabel
    Set CommonCodeLabels = New Collection
    
    For Each oneLabel In Array(Me.Label1, Me.Label2, Me.Label3)
        Set oneCommonLabel = New clsCommonLabel
        Set oneCommonLabel.myLabel = oneLabel
        CommonCodeLabels.Add oneCommonLabel, Key:=oneLabel.Name
    Next oneLabel
End Sub

Private Sub UserForm_Terminate()
    Dim oneCommonLabel As clsCommonLabel
    For Each oneCommonLabel In CommonCodeLabels
        Set oneCommonLabel = Nothing
    Next oneCommonLabel
    Set CommonCodeLabels = Nothing
End Sub

mikerickson · Aug 14, 2011

If you want everything in one package, you can treat the userform like a class module and have it to double duty, one use as the interface that the user sees, the other use(s) as class modules for the (unshown) instances of custom Labels.

Note that in this approach, asigning the Labels to the class objects is done in the Activate event NOT in the Initialize event. This is so that only the visible instance of Userform1 creates new instances of Userform1 (the un-shown ones that are containers for the labels), preventing an infinite cascade.
Also note that in the Terminate event, oneCommonLabel needs to be UnLoaded rather than being set to Nothing.

Code:

Rem code in userform module

Dim CommonCodeLabels As Collection
Public WithEvents CommonLabel As MSForms.Label

Private Sub UserForm_Activate()
    Dim oneLabel As Variant
    Dim oneCommonLabel As UserForm1
    
    For Each oneLabel In Array(Me.Label1, Me.Label2, Me.Label3)
        Set oneCommonLabel = New UserForm1
        Set oneCommonLabel.CommonLabel = oneLabel
        CommonCodeLabels.Add oneCommonLabel, Key:=oneLabel.Name
    Next oneLabel
End Sub

Private Sub UserForm_Initialize()
    Set CommonCodeLabels = New Collection
End Sub

Private Sub UserForm_Terminate()
    Dim oneCommonLabel As UserForm1
    For Each oneCommonLabel In CommonCodeLabels
        Unload oneCommonLabel
    Next oneCommonLabel
    Set CommonCodeLabels = Nothing
End Sub

Private Sub CommonLabel_Click()
    MsgBox "You clicked on " & CommonLabel.Name
End Sub

Jaafar Tribak · Aug 15, 2011

I tend to use Mike's second approach to keep the code entirely encapsulated inside the userform module.

However, I would initialize the CommonCodeLabels collection in the Activate event to avoid creating unnecessary extra collections. Also, I would tag the visible userform instance and do the cleanup once in the QueryClose event.

Code:

Rem code in userform module

Private CommonCodeLabels As Collection
Public WithEvents CommonLabel As MSForms.Label

Private Sub UserForm_Activate()

    Dim oneLabel As Variant
    Dim oneCommonLabel As UserForm1
    
    [B]Me.Tag = "Visible_Instance"[/B]
    
    [B]Set CommonCodeLabels = New Collection[/B]
    
    For Each oneLabel In Array(Me.Label1, Me.Label2, Me.Label3)
        Set oneCommonLabel = New UserForm1
        Set oneCommonLabel.CommonLabel = oneLabel
        CommonCodeLabels.Add oneCommonLabel, Key:=oneLabel.Name
    Next oneLabel

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Dim oneCommonLabel As UserForm1
    
    [B]If Me.Tag <> "Visible_Instance" Then Exit Sub[/B]
    
    For Each oneCommonLabel In CommonCodeLabels
        Unload oneCommonLabel
    Next oneCommonLabel
    
    Set CommonCodeLabels = Nothing
    
   [B] 'run other code here
[/B]
End Sub


Private Sub CommonLabel_Click()
    MsgBox "You clicked on " & CommonLabel.Name
End Sub

mikerickson · Aug 15, 2011

.ActiveControl Is Nothing is another test for the visible instance.
But I'm not sure that Test&Branch from If..Then saves time vs. the Test&Branch involved in a loop of zero iterations.

Jaafar Tribak · Aug 15, 2011

mikerickson said:
.ActiveControl Is Nothing is another test for the visible instance.
But I'm not sure that Test&Branch from If..Then saves time vs. the Test&Branch involved in a loop of zero iterations.

That would only work if there is a control on the userform that has the focus. the Tag Property works regardless.

Jaafar Tribak · Aug 15, 2011

The if .... then part is there to ensure that any subsequent code runs only once for it would potentially create problems if the code ran once for every loaded userform.

PetLahev · Aug 15, 2011

Hi Jaafar and Mike

thanks guys. It's pity that the Raiseevent doesn't work in that case, I'd like to use it. Actually it works but only for the last added item in the collection ...

Never mind, you refreshed my brain I totaly forgot about this technique

to get the post complete, here is my code where it works

Code:

' [I]Standard Module[/I]
Sub TestForm()
    ' JUST EXAMPLE
    Dim myForm As UserForm1
    
    Set myForm = New UserForm1
    myForm.AddLabel
    myForm.Show    
End Sub

Code:

' UserForm
Private CommonCodeLabels        As Collection
Public WithEvents CommonLabel   As MSForms.Label

Private Sub UserForm_Activate()

    Dim oneLabel As Variant
    Dim oneCommonLabel As UserForm1
        
    Me.Tag = "Visible_Instance"
    
    Set CommonCodeLabels = New Collection
    
    For Each oneLabel In Me.Controls
        If TypeName(oneLabel) = "Label" Then
            Set oneCommonLabel = New UserForm1
            Set oneCommonLabel.CommonLabel = oneLabel
            CommonCodeLabels.Add oneCommonLabel, Key:=oneLabel.Name
        End If
    Next oneLabel
        
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Dim oneCommonLabel As UserForm1
    
    If Me.Tag <> "Visible_Instance" Then Exit Sub
    
    For Each oneCommonLabel In CommonCodeLabels
        Unload oneCommonLabel
    Next oneCommonLabel
    
    Set CommonCodeLabels = Nothing
    
    'run other code here

End Sub

Private Sub CommonLabel_Click()
    MsgBox "You clicked on " & CommonLabel.Name
End Sub

Public Sub AddLabel()
    ' JUST EXAMPLE
    Dim lbl As MSForms.Label
    Set lbl = Me.Controls.Add("Forms.Label.1")
    
    lbl.Width = 90
    lbl.Height = 20
    lbl.Top = 95
    lbl.Left = 43
    lbl.Caption = "New label added"
    lbl.Visible = True
    
End Sub

RoryA · Aug 15, 2011

It only works for the last item because you only have one lblEvents variable whose click event you are trapping. I'm not sure I really see the point of using a WithEvents variable to raise an event back to the class that created it, so I can't suggest anything other than what you have already been given.

PetLahev · Aug 15, 2011

rorya said:
I'm not sure I really see the point of using a WithEvents variable to raise an event back to the class that created it, so I can't suggest anything other than what you have already been given.

I was just thinking to use the RaiseEvent for the first time in my whole life..

Due to that I probably choose more complicated way than was necessary..

The app is quite simple.
1, the form always contains the first controls set (3 labels)
like "lblName1", "lblDescription1", "lblSomething1"
2, my code reads an XML file and according data in this file will create another controls set (it can be 0 - up 50). So you can see up 50 labels set with these names
"lblName2", "lblDescription2", "lblSomething2"
......
"lblName50", "lblDescription50", "lblSomething50"

3, next to each controls set, I have an option button, if you select one option button, it means you have selected this controls set. The option buttons have names like
"opb1", "opb2" ...... "opb50"

4, finally I wanted to use RaiseEvent (from the common labels class) to tell my userform the name of clicked label like "lblName13" or "lblDescription26"
and in the callback on the form I just take the last number and select appropriate option button = "opb13" or "opb26"

That's it

RaiseEvent is fired only for the last control

PetLahev

New Member

Excel Facts

mikerickson

MrExcel MVP

mikerickson

MrExcel MVP

Jaafar Tribak

Well-known Member

mikerickson

MrExcel MVP

Jaafar Tribak

Well-known Member

Jaafar Tribak

Well-known Member

PetLahev

New Member

RoryA

MrExcel MVP, Moderator

PetLahev

New Member

Similar threads

Forum statistics

Share this page

RaiseEvent is fired only for the last control

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock