Applying Same Code to Multiple Labels

jay913

New Member
Joined
Aug 17, 2007
Messages
3
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
If you use Labels from the Forms Toolbar, you can assign them this macro (in a General module):

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top