Lost it!

[Mouses]

Hello Excel Black-Belts!

I'm just gutted because I really thought I'd saved the info I need now, but I can't find it and I really need to get on with the project so I'm hoping that there's someone out there that can help.

I have a bunch of check boxes on a sheet, which are grouped under 4 headers (year, month, to and from). I want to be able to put a master check/uncheck all box at the top of each of a the 4 groups to save time if a user wants, say, all months in a given year; or conversely unelected an entire set. I would also like the code that greys a master box when the group isn't completely selected.

I can can rename the checkboxes with a group name as a prefix (ie Month1 or Year1, as an example) if that helps to define the code for the 4 macros that I'll need, and name the masters "yearall", or "monthall" or something like that.

I'm appealing to all VBA gurus out there to help me out with the simplest code for this, and I'll try not to lose it this time!

Thanks all, in anticipation.

Mouse

 

[mole999]

should be like all the other checkboxs, but on the final one, you say if box five = true then
box1 = true
box2 = true etc

 

[Mouses]

Tanks for the quick reply, Mole! I'm afraid I'm totally rubbish where VBA is concerned. I know how to hook up a macro to the code that I've found on the net and that's about it. I have no idea how to just "say" if 5 is true then so are 1 through 4, and I still need to know how to grey out 5 if not all 1 to 4 are selected.

 

[MickG]

What sort are your check boxes "ActiveX" of "Forms"

 

[Mouses]

I think it's forms, Mick? I'll be honest with you, I've never understood the difference! I think I used the ones from that top section which is Forms.

 

[mole999]

what vba do you have for your checkboxes already?

 

[Mouses]

Let us assume "Nada", Mole. I tried today to work it out from what I could find on the net and I wound up checking ALL boxes instead defined groups (probably because there was some long like activesheet.checkboxes in the code). I did get something that did greyed out a box at the head of a group so that was cool, but I've no idea how to integrate it and don't have it to hand now.

 

[MickG]

This code iis based on "Foms" Checkboxes and works, Based on the fact that each SET of checkboxes are set in there own individual columns
Place this code in the Master checkbox at the top of each column.(Altering only the Checkbox name)
When the Master checkbox is checked , if some of the related checkboxes are checked and others not, then the top cell in that column will turn "Yellow". If all the checkboxes in that set are checked the same, then when the Master checkbox is checked , they will all be set at true or False as required.

[COLOR="Navy"]Sub[/COLOR] CheckBox5_Click()
[COLOR="Navy"]Dim[/COLOR] Cb [COLOR="Navy"]As[/COLOR] Object, shp [COLOR="Navy"]As[/COLOR] Object, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Cb = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object
Cells(1, Cb.TopLeftCell.Column).Interior.ColorIndex = xlNone
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] shp [COLOR="Navy"]In[/COLOR] ActiveSheet.Shapes
  [COLOR="Navy"]If[/COLOR] shp.FormControlType = xlCheckBox And Cb.TopLeftCell.Column = shp.TopLeftCell.Column [COLOR="Navy"]Then[/COLOR]
     [COLOR="Navy"]If[/COLOR] Not shp.Name = Cb.Name [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Temp = 0 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not Temp = shp.OLEFormat.Object.Value [COLOR="Navy"]Then[/COLOR]
                Cells(1, Cb.TopLeftCell.Column).Interior.ColorIndex = 6
                [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
                Temp = shp.OLEFormat.Object.Value
     [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] shp [COLOR="Navy"]In[/COLOR] ActiveSheet.Shapes
    [COLOR="Navy"]If[/COLOR] shp.FormControlType = xlCheckBox And Cb.TopLeftCell.Column = shp.TopLeftCell.Column [COLOR="Navy"]Then[/COLOR]
          [COLOR="Navy"]If[/COLOR] Not shp.Name = Cb.Name [COLOR="Navy"]Then[/COLOR]
              shp.OLEFormat.Object.Value = Cb.Value
          [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mouses]

Thanks Mick, I'm not able test it just now but my check boxes are in columns so it should work, by the sounds of things. Thank you.

I was just after getting the little check box for each master to grey rather than the whole cell. I found the following in my phone notes a few minutes ago which is the code that I manage to get working for the mixed state of a master box but I had no idea how to integrate with the checking and unchecking code. See where it says "activesheet" I don't want it to be on all boxes in the active sheet, just the ones in a group.

Sub Mixed_State()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 1").Value And ActiveSheet.CheckBoxes("Check Box 1").Value <> 2 Then
ActiveSheet.CheckBoxes("Check Box 1").Value = 2
Exit For
Else
ActiveSheet.CheckBoxes("Check Box 1").Value = CB.Value
End If
Next CB
End Sub

 

[Mouses]

And this is the code that I had that check ALL the boxes on the page, instead of just the ones in a group. I just don't know how to modify it to do that!

Sub SelectAll_Click()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
CB.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
End If
Next CB
End Sub