Change UserForm Label Color on click...code shared with multiple Labels

[zeeunit1]

Hello, and Thank You in Advance.

I have an excel user form with numerous labels (from the form tools). When you click on the label, it initiates the code below and cycles through three colors. I have used this in the past and a friend of my had simplified the coding to pull from one location ("call..." I believe), however, I can't for the life of me remember how to write this to refer each label click to the same seven-ish lines of code to save me from repeating this code 100 times. I want the click to only affect the label that was clicked, not change them all with one click.

Again, thank you.

Tim.

[Private Sub Label4_Click()
If Label4.BackColor = &H8000000F Then
Label4.BackColor = vbYellow
ElseIf Label4.BackColor = vbYellow Then
Label4.BackColor = vbRed
ElseIf Label4.BackColor = vbRed Then
Label4.BackColor = &H8000000F
End If
End Sub]

 

[My Aswer Is This]

So your wanting this same code to work in 100 labels without writing this same code 100 times.
Is that correct?

 

[Norie]

Try this.

1 Create a new Class Module name CLabels.

2 Paste this code in the CLablels module.

Option Explicit

Public WithEvents mLabelGroup As MSForms.Label

Private Sub mLabelGroup_Click()
    If mLabelGroup.BackColor = &H8000000F Then
        mLabelGroup.BackColor = vbYellow
    ElseIf mLabelGroup.BackColor = vbYellow Then
        mLabelGroup.BackColor = vbRed
    ElseIf mLabelGroup.BackColor = vbRed Then
        mLabelGroup.BackColor = &H8000000F
    End If
End Sub

3 Add this at the top of the userform module.

Dim Labels() As New CLabels

4 In the userform Initialize event add this code.

Dim ctl As MSForms.Control
Dim cnt As Long
    ' other declarations

    ReDim Labels(1 To Me.Controls.Count)

    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.Label Then
            cnt = cnt + 1
            Set Labels(cnt).mLabelGroup = ctl
        End If
    Next ctl

    If cnt > 0 Then
        ReDim Preserve Labels(1 To cnt)
    End If

Note, this will 'attach' the click event in the class module to all the labels on the form.

 

[zeeunit1]

Yes, Please.

 

[My Aswer Is This]

I'm not sure how to do that.

You could write a Sub in the UserForm
And use Call in each Label

But I do not know how to refer to the Control Clicked On.

I would think if you click on Label1 you could use Me.ActivControl.BackColor=

But Me.ActiveControl does not work.

I'm not sure how to refer to the control clicked on.

If I knew how to I could write a script like

If Me.ActiveControl Back color is:

Maybe someone else here at Mr. Excel will have a answer.

 

[zeeunit1]

@Norie - Yes, that is what I am looking for. If all fails, I will just repeat the code as needed. One drawback to that is, if I decide to change a color I will have to change it throughout. If there is only one source of code, It would make that easier.

Thank You for Taking the time to respond.

 

[zeeunit1]

Thank You for responding. I wish that I still had the code from before, lol. I have searched high and low. I will see if there is a way to make the If statement you gave can be manipulated in some way.

Have a Great Day.

 

[zeeunit1]

@Norie
I apologize...I replied to the wrong response. I Thank You for your quick response and code, however, I am looking for something that changes the color to ONLY the label that is clicked. If you have a solution to that, I would Appreciated it immensely!
Thanks

 

[My Aswer Is This]

Norie:
Please teach me how to do this:
I have never done this:

Create a new Class Module name CLabels.
​

 

[Fluff]

Norie's code does only change the label that is clicked.

Another option might be

Private Sub Label1_Click()
 Call test1(Me.Label1)
End Sub
Private Sub Label2_Click()
   Call test1(Me.Label2)
End Sub

Sub test1(Lbl As MSForms.label)
If Lbl.BackColor = &H8000000F Then
   Lbl.BackColor = vbYellow
ElseIf Lbl.BackColor = vbYellow Then
   Lbl.BackColor = vbRed
ElseIf Lbl.BackColor = vbRed Then
   Lbl.BackColor = &H8000000F
End If
End Sub

But you would need to add a click event for each label as shown