Excel Checkbox created from Label in the Control Toolbox

[blackorchids2002]

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]

Try something like this...

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.

 

[blackorchids2002]

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...

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]

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]

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]

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]

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]

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]

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]

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 ?