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.

 

[bobsan42]

i can't see the checkboxes in your example, but I am not sure how this code does the job for you.
The code you posted will only unselect checkboxes if they are well scattered on the sheet - boxes matching the row or column of the clicked checkbox will not be unselected.
Using the index property is much reliable in this context.
for your particular request try this modification:

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 > 8) = (c.TopLeftCell.Column > 8) Then
            If cx.Index <> c.Index Then cx.Value = xlOff
        End If
    Next cx
End Sub

 

[usnapoleon]

i can't see the checkboxes in your example, but I am not sure how this code does the job for you.
The code you posted will only unselect checkboxes if they are well scattered on the sheet - boxes matching the row or column of the clicked checkbox will not be unselected.
Using the index property is much reliable in this context.
for your particular request try this modification:

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 > 8) = (c.TopLeftCell.Column > 8) Then
            If cx.Index <> c.Index Then cx.Value = xlOff
        End If
    Next cx
End Sub

Hi Bobsan42 - Sorry I did a preview of the message and it didnt dawn on me that the actual checkboxes dont show! doh!

Above is how the current sheet looks. The row and column numbers are as from my opening post, it just doesn't do justice to the visual aspect of it.

The code I shared would affect only the same row I was working on. If i checked Canyon Grille's box, and then check The Shop, the Canyon Grille would uncheck. It worked just fine in that regard. There were just the 5 columns of the 'Departments'.
Then I decided to add the 2nd group of checkboxes, the 'Voucher Type'. This is where it started messing up: if I check Canyon Grille, and then Starbucks, the Canyon Grille unchecks - I dont want that. The 'Department' section can only have 1 checked box per row, so if Canyon Grille gets checked then any other checked department should uncheck on that row. The 'Voucher Type' section functions the same way, so if Resort Wide is checked then any other Voucher type should uncheck on that row. A Department checked-box should not affect a Voucher checked-box, and vice versa.

Reviewing the VBA above, will this work now that I've given more details? Will the macro be assigned to all check boxes? Thank you very much for helping me with this!!

 

[bobsan42]

Did you try the code i posted?

 

[usnapoleon]

It does work! Yay! Sorry for not testing it first, I just wasnt sure mainly if it was a 2nd macro or a total replacement. I dont know enough about VBA to simply look at it and know. Thank you very very much!!!! @bobsan42

 

[usnapoleon]

Did you try the code i posted?

Hi again
Actually, it is having a problem. it is properly unchecking the row and group it's related to, but it's also unchecking the column, so it's affecting other rows. Do you know why?

 

[bobsan42]

yes, now that i see what you mean,i think.
try like this:

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

if each row is a separate group.

 

[usnapoleon]

yes, now that i see what you mean,i think.
try like this:

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

if each row is a separate group.

Sorry, this was not working either.

When I check one box, then another (in the same group), it does not uncheck the first box. So I'm able to have multiple boxes at the same time.

 

[kevin9999]

Please try the following on a copy of your workbook.

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 c.TopLeftCell.Column >= 3 And c.TopLeftCell.Column <= 7 Then
            If cx.TopLeftCell.Column >= 3 And cx.TopLeftCell.Column <= 7 Then
                If cx.TopLeftCell.Column <> c.TopLeftCell.Column _
                And cx.TopLeftCell.Row = c.TopLeftCell.Row _
                Then cx.Value = xlOff
            End If
        End If
        If c.TopLeftCell.Column >= 9 And c.TopLeftCell.Column <= 13 Then
            If cx.TopLeftCell.Column >= 9 And cx.TopLeftCell.Column <= 13 Then
                If cx.TopLeftCell.Column <> c.TopLeftCell.Column _
                And cx.TopLeftCell.Row = c.TopLeftCell.Row _
                Then cx.Value = xlOff
            End If
        End If
    Next cx
End Sub

 

[bobsan42]

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.