VBA Macro help with checkboxes involved

[usnapoleon]

Hello
I have an existing Macro that I got from a cool person on here where, upon checking 1 box it would uncheck any others, thus preventing errors. I need some help again, and the difference now is that I have 2 sets of checkboxes that are independent of each other. However, when I check 1 on one side, it unchecks the box on the other side. I'll show you:

Voucher Spreadsheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
16
17	Voucher Itemization
18			Department						Voucher Type
19			CG	MP	PH	SH	TH		RW	FV	SB	TH		Not Reduced
20	Cashier Name	Ck #	Canyon Grille	Market Place	Pizza Hut	The Shop	Theater	Dept	Resort Wide	Food Voucher	Starbucks	Theater	Type	AMOUNT
21								SH						5.00
1

Cell Formulas
Range	Formula
H21	H21	=IFERROR(INDEX($C$19:$G$19,MATCH(TRUE,C21:G21,0)),"")
M21	M21	=IFERROR(INDEX($I$19:$L$19,MATCH(TRUE,I21:L21,0)),"")

columns C-G are the original checkboxes, and the VBA Macro was built for them originally. I added the 2nd set of I-L to the spreadsheet. C-G are independent of columns I-L. There has to be 1 box checked in each group but it's not letting me.

Here is the VBA macro coding that is assigned:

Option Explicit
Sub Change_Check_Boxes()
    Dim c As Excel.CheckBox, cx As Excel.CheckBox
    Set c = ActiveSheet.CheckBoxes(Application.Caller)
    For Each cx In ActiveSheet.CheckBoxes
        If cx.TopLeftCell.Column <> c.TopLeftCell.Column _
        And cx.TopLeftCell.Row = c.TopLeftCell.Row Then cx.Value = xlOff
    Next cx
End Sub

I know nothing of VBA so I couldnt even attempt to review and try to modify this. I have a feeling I need a 2nd macro for the new checkboxes in columns I-L. I'm just guessing though.

 

[usnapoleon]

what do you consider a group of checkboxes?
the code works, maybe what it does is not what you expect.
All checkboxes on one row to the left or right of column H are considered a group. The code will allow you to have 1 or 0 checked boxes in a group.
just tested kevin9999's code it also works in (almost) the same way. so you have to make one of these two work for you.

Hi Bobsan42! Sorry for the delay, I was out most of the day. To answer your question, I have 'boxed' the top 2 groups on each side visually so you can see.

I tried Kevin's code, and it was working (the results are above). Checking a box in the top left group had no effect to the groups below, nor the groups to the right.

Weirdly, the last code you gave is now working too. That is so strange. What was happening was that the groups on either side were working independently of each other (as desired) but vertically they were interacting... like the top left group would affect the groups below it (unchecking them). Now, it's not doing it!

You code works, and you are awesome! Thank you, and thank you @kevin9999 . I am in clear waters now, thank you thank you! Months of planning and spreadsheet work is going to be wrapped up with this, thank you both!

I will have to learn some VBA so I can 'read' the code. I just like learning thank you again!

 

[usnapoleon]

@bobsan42
Sorry to bother you, I had one small question about the VBA you gave me on this. I was asked to add more columns to both sections, such that it goes from looking like this:

to looking like this:

Do I need to edit any part of this to make it work with the additional columns?

Sub Change_Check_Boxes()
    Dim cb As Excel.CheckBox, cbx As Excel.CheckBox
    Dim c As Range, cx As Range
    Dim i As Long
 
    Set cb = ActiveSheet.CheckBoxes(Application.Caller)
    i = cb.Index
    Set c = cb.TopLeftCell
 
    For Each cbx In ActiveSheet.CheckBoxes
        If i <> cbx.Index and cbx.Value = xlOn Then
            Set cx = cbx.TopLeftCell
            If c.Row = cx.Row Then
                If (c.Column > 8) = (cx.Column > 8) Then
                    cbx.Value = xlOff
                End If
            End If
        End If
    Next cbx
End Sub

I'm thinking that the 8 in the code represents something and may need changing, but I'm out of my element to make guesses. Sorry again!

I thought boxes would be cool to use, but 31 tabs of this makes the spreadsheet take a bit to load up... and my lack of expertise is hurting me! Not sure any other type of spreadsheet would be as easy for input/usage though. Oh well, hahaha.

 

[bobsan42]

8 is column H.
If now the black Dept column is i change to 9, for J - 10, etc.

 

[usnapoleon]

8 is column H.
If now the black Dept column is i change to 9, for J - 10, etc.

Makes sense!!! Thank you!!!!!!

 

[bobsan42]

You're welcome.