• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

One checkbox that unchecks and disables others in its group whilst selected

T

Tom Nice

New Member
Hi there,


I'm trying to create a macro that will uncheck checkboxes in a group when one is selected - this checkbox is labelled as (All). When selected, I want the other checkboxes to be disabled so that it is only the (All) box that can be checked. Once that is unchecked, the other cbs can be checked once again.


I've got to the stage where clicking the (All) box unchecks the other cbs but have had trouble in disabling other cbs and getting them to enable again.


Here is my code (I'm very new to VBA so is quite messy and I have named the cbs from CB_07 to CB_14 with CB_07 being the (All) checkbox):



Code: 
 Sub ProcessGrps()


 Dim shpChkBox As Shape

 

 Set shpChkBox = ActiveSheet.Shapes(Application.Caller)

 If shpChkBox.ControlFormat.Value = xlOn Then  'xlOn is Checked

 

 Select Case shpChkBox.Name


 Case "CB_07"

 With ActiveSheet

 .Shapes("CB_08").ControlFormat.Value = xlOff  'xlOff is Unchecked

 .Shapes("CB_09").ControlFormat.Value = xlOff  'xlOff is Unchecked

 .Shapes("CB_10").ControlFormat.Value = xlOff  'xlOff is Unchecked

 .Shapes("CB_11").ControlFormat.Value = xlOff  'xlOff is Unchecked

 .Shapes("CB_12").ControlFormat.Value = xlOff  'xlOff is Unchecked

 .Shapes("CB_13").ControlFormat.Value = xlOff  'xlOff is Unchecked

 .Shapes("CB_14").ControlFormat.Value = xlOff  'xlOff is Unchecked

 

 End With


 

 End Select

 ActiveSheet.Shapes(shpChkBox.Name).ControlFormat.Value = xlOn  'xlOn is Checked

 

 

 End If

End Sub

Thanks in advance for any help, it is greatly appreciated!


Tom
 
I have made further developments with the code - now I can make sure the other cbs are disabled while CB_07 is checked but only for a few clicks and then they become enabled again. Do you guys know how to keep them disabled while CB_07 is checked?

Code: 
Sub ProcessGrps() 
     '
    Dim shpChkBox As Object 
     
     
    Set shpChkBox = ActiveSheet.Shapes(Application.Caller) 
     
    If ActiveSheet.Shapes("CB_07").ControlFormat.Value = xlOn Then 
        With ActiveSheet 
            .Shapes("CB_08").ControlFormat.Enabled = False 'xlOff is Unchecked
            .Shapes("CB_09").ControlFormat.Enabled = False 'xlOff is Unchecked
            .Shapes("CB_10").ControlFormat.Enabled = False 'xlOff is Unchecked
            .Shapes("CB_11").ControlFormat.Enabled = False 'xlOff is Unchecked
            .Shapes("CB_12").ControlFormat.Enabled = False 'xlOff is Unchecked
            .Shapes("CB_13").ControlFormat.Enabled = False 'xlOff is Unchecked
            .Shapes("CB_14").ControlFormat.Enabled = False 
        End With 
    Else 
        With ActiveSheet 
            .Shapes("CB_08").ControlFormat.Enabled = True 'xlOff is Unchecked
            .Shapes("CB_09").ControlFormat.Enabled = True 'xlOff is Unchecked
            .Shapes("CB_10").ControlFormat.Enabled = True 'xlOff is Unchecked
            .Shapes("CB_11").ControlFormat.Enabled = True 'xlOff is Unchecked
            .Shapes("CB_12").ControlFormat.Enabled = True 'xlOff is Unchecked
            .Shapes("CB_13").ControlFormat.Enabled = True 'xlOff is Unchecked
            .Shapes("CB_14").ControlFormat.Enabled = True 
        End With 
         
    End If 
     
    If ActiveSheet.Shapes("CB_07").ControlFormat.Value = xlOn Then 'xlOn is Checked
         
         
        Select Case shpChkBox.Name 
             
             
        Case "CB_07" 
            With ActiveSheet 
                .Shapes("CB_08").ControlFormat.Value = xlOff 'xlOff is Unchecked
                .Shapes("CB_09").ControlFormat.Value = xlOff 'xlOff is Unchecked
                .Shapes("CB_10").ControlFormat.Value = xlOff 'xlOff is Unchecked
                .Shapes("CB_11").ControlFormat.Value = xlOff 'xlOff is Unchecked
                .Shapes("CB_12").ControlFormat.Value = xlOff 'xlOff is Unchecked
                .Shapes("CB_13").ControlFormat.Value = xlOff 'xlOff is Unchecked
                .Shapes("CB_14").ControlFormat.Value = xlOff 'xlOff is Unchecked
                 
                 
            End With 
             
             
        End Select 
        ActiveSheet.Shapes(shpChkBox.Name).ControlFormat.Value = xlOn 'xlOn is Checked
         
         
         
    End If 
End Sub

Many thanks!
 
Hi Tom,

Try something like this.

Code: 
.Shapes("CB_14").ControlFormat.Value = xlOff
.Shapes("CB_14").ControlFormat.Enable = False

For each shape.

Regards.
 
Unfortunately that didn't work, the same problem of clicking a few times on the checkbox and it checks is still occuring. Cheers
 
Hi Tom ,

I think troubleshooting code , especially where it involves objects is somewhat difficult , since testing will not be possible unless one is willing to take the trouble to insert at least a few objects.

If you can upload your workbook , it will make it easier for others to help.

Narayan
 
Hi Tom ,

Can you check your file ?

I have not used your macro. As far as the other checkboxes are concerned , I have deleted the macro assignment ; you will have to do what ever is necessary.

Narayan
 

Attachments

Both work a treat, thanks but I want to go with form controls as opposed to activex.

Narayan, if I wanted to grey out the deselected checkboxes (ones that cannot be clicked when (all) is checked) I have tried using

Code: 
 .CheckBoxes(i).Fill.ForeColor = RGB(128, 128, 128)

however this comes up with the object doesn't support this property or method error. Any ideas?

Thanks
 
Hi Narayan,

Thank you again, this is great. I have one more request if I may - the checkboxes will be in groups on different sheets. The particular group that CB_07 is part of spans from CB_07 to CB_14. So on the worksheet I'm working on, it would be great to apply the macro so that it only affects a group of checkboxes.

I have tried the following code but keep getting an index into specified collection is out of bounds error:

Code: 
Sub CB_07_Click()
Dim arChkBoxes As Variant
Dim chkBoxRng As Object
Set myWS = Worksheets(1)

    arChkBoxes = Array("CB_08", "CB_09", "CB_10", "CB_11", "CB_12", "CB_13", "CB_14")
    Set chkBoxRng = myWS.Shapes.Range(arChkBoxes)

Any pointers on how to apply this to different groups on worksheet/s would be great. Thank you!
 
Hi Tom ,

Can you explain in a little more detail , or upload a sample workbook ?

Are you having several groups of checkboxes on one sheet , or are they on different worksheets ?

If they are on one sheet , how are the lower level checkboxes tied into the upper level checkboxes ?

Narayan
 
Hi Narayan,

There will be several groups of checkboxes on different worksheets as you will see in the uploaded workbook. Ideally, I would like to run macros on each (All) checkbox so that this process applies to them. The code is in module 5.

Thanks for your help,

Tom
 

Attachments

Hi Tom ,

See the attached file.

I have done this for 2 of the 3 sheets ; I am sure you can do it for the remaining sheet as well as any new sheets you might add.

Narayan
 

Attachments

Hi Narayan,

Many thanks for your help. If I wanted to change the colour and size of the label's font how would I go about this?

I've tried using Sh.Labels(k).Font.Size = 10 and Sh.Labels(k).Font.Color = RGB(128, 128, 128) but get a run-time error '438. Any final tips?

Cheers,

Tom
 
You must log in or register to reply here.
Back
Top