Excel Checkbox created from Label in the Control Toolbox

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
117
Hi Masters,

I'll be needing your help again...

I have created Checkboxes using Label from the Control Toolbox because if I use the checkbox it cannot be resized if you will put it in the worksheet. I followed the instructions as per link below and was able to achieve what I need. In "Sheet1", I have created 16 checkboxes and grouped as per section.

http://excel.tips.net/T002282_Resizing_Checkboxes.html

My problem now is that, how will I restrict the user if he already selected 1 checkbox in that section. Let say, section 1 is for the payment options. In section 1, I have 3 checkboxes as outlined below:

Section1: Payment Options

Checkbox 1: Cash Dep
Checkbox 2: COD
Checkbox 3: Credit

The goal is if user has selected "Checkbox 1: Cash", then, Checkbox 2 & 3 will be blocked/deactivated so he will not be able to select more than 2 checkboxes. If the user will select checkbox 2, then, checkbox 1&3 will be blocked/deactivated. Same scenario if the user is to select checkbox 3, then checkbox 1&2 will be blocked/deactivated.

Below is the code placed in Sheet1 for the checkbox I created in Section 1.


Private Sub CB1_Label_Click()
If CB1_Label.Caption = Chr(254) Then
CB1_Label.Caption = Chr(168)
Else
CB1_Label.Caption = Chr(254)
End If
End Sub

Private Sub CB2_Label_Click()
If CB2_Label.Caption = Chr(254) Then
CB2_Label.Caption = Chr(168)
Else
CB2_Label.Caption = Chr(254)
End If
End Sub

Private Sub CB3_Label_Click()
If CB3_Label.Caption = Chr(254) Then
CB3_Label.Caption = Chr(168)
Else
CB3_Label.Caption = Chr(254)
End If
End Sub



Many thanks in advance...

thanks,
blackorchids2002
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Try something like this...

Code:
Private Sub CB1_Label_Click()
If CB1_Label.Caption = Chr(254) Then
CB1_Label.Caption = Chr(168)
[COLOR=#ff0000]CB2_Label.Caption = Chr(254)[/COLOR]
[COLOR=#ff0000]CB3_Label.Caption = Chr(254)[/COLOR]
Else
CB1_Label.Caption = Chr(254)
End If
End Sub


Private Sub CB2_Label_Click()
If CB2_Label.Caption = Chr(254) Then
CB2_Label.Caption = Chr(168)
[COLOR=#ff0000]CB1_Label.Caption = Chr(254)[/COLOR]
[COLOR=#ff0000]CB3_Label.Caption = Chr(254)[/COLOR]
Else
CB2_Label.Caption = Chr(254)
End If
End Sub


Private Sub CB3_Label_Click()
If CB3_Label.Caption = Chr(254) Then
CB3_Label.Caption = Chr(168)
[COLOR=#ff0000]CB1_Label.Caption = Chr(254)[/COLOR]
[COLOR=#ff0000]CB2_Label.Caption = Chr(254)[/COLOR]
Else
CB3_Label.Caption = Chr(254)
End If
End Sub
This doesn't block\deactivate the others. It de-selects them so only one of the three is selected.
 
Last edited:

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
117
Hi AlphaFrog,

I have tried your code and selected checkbox 1 and also tried selecting checkbox two. Both checkboxes are checked. Then when I tried to unselect checkbox to, the checkmark moves to checkbox 3. How is it possible that only one check is selected?


thanks,
Lhe


Try something like this...

Code:
Private Sub CB1_Label_Click()
If CB1_Label.Caption = Chr(254) Then
CB1_Label.Caption = Chr(168)
[COLOR=#ff0000]CB2_Label.Caption = Chr(254)[/COLOR]
[COLOR=#ff0000]CB3_Label.Caption = Chr(254)[/COLOR]
Else
CB1_Label.Caption = Chr(254)
End If
End Sub


Private Sub CB2_Label_Click()
If CB2_Label.Caption = Chr(254) Then
CB2_Label.Caption = Chr(168)
[COLOR=#ff0000]CB1_Label.Caption = Chr(254)[/COLOR]
[COLOR=#ff0000]CB3_Label.Caption = Chr(254)[/COLOR]
Else
CB2_Label.Caption = Chr(254)
End If
End Sub


Private Sub CB3_Label_Click()
If CB3_Label.Caption = Chr(254) Then
CB3_Label.Caption = Chr(168)
[COLOR=#ff0000]CB1_Label.Caption = Chr(254)[/COLOR]
[COLOR=#ff0000]CB2_Label.Caption = Chr(254)[/COLOR]
Else
CB3_Label.Caption = Chr(254)
End If
End Sub
This doesn't block\deactivate the others. It de-selects them so only one of the three is selected.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Hi AlphaFrog,

I have tried your code and selected checkbox 1 and also tried selecting checkbox two. Both checkboxes are checked. Then when I tried to unselect checkbox to, the checkmark moves to checkbox 3. How is it possible that only one check is selected?


thanks,
Lhe
It worked for my tests. Only one was selected at any time. I don't know what else to tell you.
 

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
117
Hi Alpha Frog,

What I did is I pasted the VB code in Sheet1 worksheet.

I checked checkbox1, then, checked checkbox 2. The two checkboxes still have check mark on it.
I was expecting that when I selected checkbox 2, checkbox 1 & 3 have no check mark on the checkboxes.

It worked for my tests. Only one was selected at any time. I don't know what else to tell you.

Thanks,
Lhe
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Hi Alpha Frog,

What I did is I pasted the VB code in Sheet1 worksheet.

I checked checkbox1, then, checked checkbox 2. The two checkboxes still have check mark on it.
I was expecting that when I selected checkbox 2, checkbox 1 & 3 have no check mark on the checkboxes.




Thanks,
Lhe
Do you have more labels on Sheet1 than just these three? This works for CB1_Label, CB2_Label, and CB3_Label.

Do you have any other code in the Sheet1 code module?
 

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
117
Thanks AlphaFrog for your reply.

Yes, in sheet 1 there are 16 checkboxes created and grouped as per section.

Thanks,
Lhe




Do you have more labels on Sheet1 than just these three? This works for CB1_Label, CB2_Label, and CB3_Label.

Do you have any other code in the Sheet1 code module?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Thanks AlphaFrog for your reply.

Yes, in sheet 1 there are 16 checkboxes created and grouped as per section.

Thanks,
Lhe
So I have to ask if you are clicking on the correct three Labels? Again, this works for CB1_Label, CB2_Label, and CB3_Label.

Do you have any other event procedures for CB1_Label, CB2_Label, and CB3_Label ?
 

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
117
Yes, I clicked the 3 labels. I even tried removing my codes for CB4 to 16 just to test but it did not work.

How I wish you can see my desktop. I will allow AMMYY for remote application...


So I have to ask if you are clicking on the correct three Labels? Again, this works for CB1_Label, CB2_Label, and CB3_Label.

Do you have any other event procedures for CB1_Label, CB2_Label, and CB3_Label ?
 

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
117
Hi, I don't have any event procedure except the code you provided.


So I have to ask if you are clicking on the correct three Labels? Again, this works for CB1_Label, CB2_Label, and CB3_Label.

Do you have any other event procedures for CB1_Label, CB2_Label, and CB3_Label ?
 

Forum statistics

Threads
1,081,727
Messages
5,360,913
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top