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

 

[blackorchids2002]

Hi AlphaFrog,

Thanks for your effort. I sorted it out and the code below is now working.

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 ?

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

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

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

 

[blackorchids2002]

Hi AlphaFrog,

Since the code below is now working, how will initialize the checkboxes without a check mark when you first opened the file?

Many thanks,
blackorchids2002

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?

Here is the vode that works now...
Private Sub CB1_Label_Click()
If CB1_Label.Caption = Chr(254) Then
CB1_Label.Caption = Chr(168)
CB2_Label.Caption = Chr(254)
CB3_Label.Caption = Chr(254)
Else
CB1_Label.Caption = Chr(254)
CB2_Label.Caption = Chr(168)
CB3_Label.Caption = Chr(168)
End If
End Sub

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

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