Applying Same Code to Multiple Labels

[jay913]

I'm sure this is probably a simple task. However, I have had no luck trying to find a short solution to my problem. I am creating a spreadsheet that requires multiple checkboxes. The standard checkboxes are way too small for my liking. Therefore, I am using the Label function utilizing the Wingdings font to get what I want. I would like to apply the same code to all my labels. However, when i copy a Label and paste it, it does not not copy the code. Can someone let me know how I can make this code work for all Labels without entering it seperatly for each one (Loop, etc)? Here is the current Code I am using:

Private Sub Label1_Click()
If Label1.Caption = Chr(252) Then
Label1.Caption = Chr(32)
Else
Label1.Caption = Chr(252)
End If
End Sub

Thank you for your help.

Jason

 

[Andrew Poulsom]

If you use Labels from the Forms Toolbar, you can assign them this macro (in a General module):

Sub Label_Click()
    With ActiveSheet.Labels(Application.Caller)
        If .Caption = Chr(252) Then
            .Caption = Chr(32)
        Else
            .Caption = Chr(252)
        End If
    End With
End Sub

 

[jay913]

Well, I tried that it does not seem to work. When I click on the label, the character remains unchanged.

The problem I have is that each time I create a new Label or copy an existing label in Excel the name changes:

Label1
Label2
Label3
Etc

The only thing that works for me is by creating a new sub routine for each individual label. This is a lot of work for 200+ checkboxes.

When I tried running the Macro from excel, VB opened up and displayed the error “400”. Am I missing a step here? Sorry, I’m not exactly experienced in Macros/VB. Thanks for your time.

 

[Andrew Poulsom]

Add a Label from the Forms Toolbar, right click it and assign it the macro I posted. If you copy paste that label the macro assignment will also be copied.

 

[jay913]

Well, it wasn’t working because I was using the Label in the Control Toolbox and not the Forms Toolbar (Like you already mentioned). It works when I created a new Label using the Forms Toolbar and ran the Macro you provided. However, I cannot edit the font. It is using the wrong character and it is extremely small. I just did some quick research and it looks like it cannot be edited. Is this true?

Is their any other way to use the Label Function from the Controls Toolbox to do what I want without retyping the code over and over for each Label (1,2,3,etc)?

Thank you,