Using checkboxes to populate a value if they are checked

[usnapoleon]

For starters, I have never used checkboxes before, so I have spent the last few hours watching videos and reading instructions and I have not been getting anywhere. Sorry to take your time.

I had an idea on a spreadsheet I'm creating, and it occurred to me that checkboxes might be the best solution for my goal. ---- if you can think of a different idea, please let me know, I'm open to going in other directions.

That aside, let me share my idea, my code, and what I'm aiming to do, and with your expertise we can see where to go from here.

My business has to write off certain expenses and our account coding breaks it out by department and the expense. My idea is for my team to input the expense code (that is the blue areas in column B) and we'd check the box for the specific department it's related to (columns c-f). Depending on the checked box, column G will be formulaed to input the department code that we see in the black row. That formula is what I need help with.

Way over to the side in column T (not shown) I have a sumif formula that will look at column G and B and give totals for the 'Amount' in column H. I am just letting you know what we're doing with the overall results.

I can write an ugly code like =if(c5=true,"0010",if(d5=true,"0430",if..... blah blah blah... I am just going off the top of my head but it would be ugly regardless. And long. And ugly. And long... haha.

I currently have the top row of checkboxes (row 5) linked to each cell... so the first one is lined to C5, literally the cell that the checkbox is located at. Unfortunately it automatically types 'TRUE' or 'FALSE'. Nothing I can do about that, other than recolor the cells to white, change the text to white and nobody would ever know. Not sure how wise this would be.

Also, I had the idea that a fail-safe might need to be in place where if one box is checked on a row, the other boxes should uncheck. I read that somewhere.

You know... I might even edit the formula in column G (whatever we can come up with)so that it gives the department AND adds the Account Code in Column B. I think that would be easy, and it makes for an easier sumif formula in column T that I mentioned previously.

As you can see, there just seems to be a lot of work needed to get this going and I'm not sure if the end result would be worth it. Looking forward to some help.

I know there are other ways I can make the spreadsheet work. For example, I can always write a key somewhere that shows the department numbers that users like myself can reference, and instead of 4 columns of checkboxes I can make a single column that is similar to column B and manually type the department number each time. I just thought it would be easier on the user and also super-cool to use checkboxes - just check a box and be done. Knowing that other solutions such as this exist is also why I'm open to going into other directions - there could be another very cool thing we can do that would make it super easy on the user.

I've talked enough, sorry... let me share my spreadsheet... i did a preview of this and noticed it didnt show the checkboxes, like I display in my snip above. Weird!!

Breck WO Template 11.23.xlsx
	A	B	C	D	E	F	G
3	▼TSW: BRECKADMIN ▼	Dept :	0011	0430	0012	0410
4	Ranahan - TSW direct bills	Acct Code	Front Desk	Engineering	Housekeeping	A&G / Admin	Dept
5			FALSE	FALSE
6
7
Monthly Activity

 

[kevin9999]

OK, let’s make a start on this. Three things:

Firstly, to hide the TRUE/FALSE in the linked cells without messing about with font colours do this: highlight all the cells, right-click & select Format Cells/Custom, and enter ;;; (3 semicolons) in the Type: box. That will hide the underlying text.

Secondly, put the following code in a standard module and assign the same macro to all the checkboxes (right-click the checkbox, select Assign Macro). Unfortunately, there’s no quick way of doing this, you’ll have to do each checkbox individually. What it does is set every other checkbox in the row of the checkbox you just clicked to FALSE.

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

Finally, use an index/match formula as shown below to return the Department number.

Book1
	A	B	C	D	E	F	G
3	▼TSW: BRECKADMIN ▼	Dept :	11	430	12	410
4	Ranahan - TSW direct bills	Acct Code	Front Desk	Engineering	Housekeeping	A&G / Admin	Dept
5							11
6							430
7							410
Sheet1

Cell Formulas
Range	Formula
G5:G7	G5	=IFERROR(INDEX($C$3:$F$3,MATCH(TRUE,C5:F5,0)),"")

Let me know if you get stuck on any of this.

 

[kevin9999]

Looks like this with the checkboxes.

 

[kevin9999]

A link to the demo file: usnapoleon.xlsm

 

[usnapoleon]

A link to the demo file: usnapoleon.xlsm

Hi Kevin
Sorry for the delay in getting back to you. I am reviewing everything you typed and will be testing it out. I just wanted to respond and say thank you in advance! Huge amounts of help as you were hitting all the right points of concern. Crossing fingers I'll keep in touch, it was important that I respond so you know you weren't wasting time helping me out without responses. Cheers!

 

[kevin9999]

Thank you for letting me know, and I look forward to finding out how you get on. (Don't forget to check out the linked file).

 

[usnapoleon]

Thank you for letting me know, and I look forward to finding out how you get on. (Don't forget to check out the linked file).

Hi Kevin
So a few things..

First.. the custom formatting... awesome, thank you.
And the formula for the lindex/match... perfect. I knew there was an easier way to do it!

I have some questions on the 2nd step. I think I did it right, especially after reviewing the linked file. But I'm not 100%. So let me show you my steps, what I saw, and what it looks like now.

I went into Developer > Macros, and created the 'Multi_CkBox_prevention' Macro, and gave it the description. Then I clicked Edit so I could add your VBA coding:

When I got to the VBA, it already had some text in there:

Sub Multi_CkBox_prevention()
'
' Multi_CkBox_prevention Macro
' When a box is checked, it is defined in Excel as 'True'.  This macro will cause the other check boxes to be 'False' to prevent multiple check boxes being checked on a single row.  This has to be assigned to EVERY individual checkbox.
'

    Application.Goto Reference:="Multi_CkBox_prevention"
End Sub

I took your code and added it so it looked like this:

Sub Multi_CkBox_prevention()
'
' Multi_CkBox_prevention Macro
' When a box is checked, it is defined in Excel as 'True'.  This macro will cause the other check boxes to be 'False' to prevent multiple check boxes being checked on a single row.  This has to be assigned to EVERY individual checkbox.
'
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


    Application.Goto Reference:="Multi_CkBox_prevention"
End Sub

When I go into it now, it shows up like this, and I dont know why it's adding all this, should I be concerned?

Sub Multi_CkBox_prevention()
'
' Multi_CkBox_prevention Macro
' When a box is checked, it is defined in Excel as 'True'.  This macro will cause the other check boxes to be 'False' to prevent multiple check boxes being checked on a single row.  This has to be assigned to EVERY individual checkbox.
'
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


    Application.Goto Reference:="Multi_CkBox_prevention"
    ActiveWorkbook.Save
    ActiveSheet.Shapes.Range(Array("Check Box 4")).Select
    ActiveSheet.Shapes.Range(Array("Check Box 3")).Select
    Selection.OnAction = "Multi_CkBox_prevention"
    ActiveSheet.Shapes.Range(Array("Check Box 171")).Select
    Selection.OnAction = "Multi_CkBox_prevention"
    ActiveSheet.Shapes.Range(Array("Check Box 47")).Select
    Selection.OnAction = "Multi_CkBox_prevention"
    ActiveSheet.Shapes.Range(Array("Check Box 3")).Select
    Selection.OnAction = "Multi_CkBox_prevention"
    ActiveSheet.Shapes.Range(Array("Check Box 69")).Select
    Selection.OnAction = "Multi_CkBox_prevention"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(R3C3:R3C6,MATCH(TRUE,RC[-4]:RC[-1],0)),"""")"
    Range("G5").Select
    Selection.AutoFill Destination:=Range("G5:G26"), Type:=xlFillDefault
    Range("G5:G26").Select
    Range("C5").Select
    Application.Goto Reference:="Change_Check_Boxes"
    Range("P16").Select
    Application.ActiveProtectedViewWindow.Edit
    ActiveSheet.Shapes.Range(Array("Check Box 9")).Select
    Application.Goto Reference:= _
        "'Breck WO Template 11.23.xlsm'!Multi_CkBox_prevention"
    Range("K9").Select
    ActiveSheet.Shapes.Range(Array("Check Box 5")).Select
    Windows("Breck WO Template 11.23.xlsm").Activate
    Application.Goto Reference:="Multi_CkBox_prevention"
End Sub

Moving on though, please confirm if I need to link each checkbox to the cell. I did it for the first row only, because it's tedious and I didnt want to spend time doing that until I was sure I had to.

Additionally, I noticed that I am still able to check off multiple boxes on a single row. I was hoping that checking one would prevent checking of others - that in order to check a different box then the current-checked box for that row would need to be unchecked. Right now, I can check mutiple boxes on a single row, and it will display in Column G the number of the left-most checked box. Like on this snip from your excel, it shows 430 because thats the left-most checked box:

 

[kevin9999]

Good to hear steps 1 & 3 are giving you what you were looking for, although I should point out that step 3 (the formula) will not work properly unless step 2 (the macro) works correctly, as the formula will always find the first instance of TRUE in the row, even if there are multiple instances of TRUE.

I have no idea where all of the rest of that code comes from, or what functionality it is adding to your workbook. What I'd like you to try is this:
1. Delete all macros in your workbook.
2. Copy only the Sub Change_Check_Boxes() macro (from post #2) into an empty module your workbook
3. Right-click each checkbox on your sheet, select Assign macro, and select the Change_Check_Boxes() macro (should be the only one on the list)
4. Test it by checking various checkboxes on your sheet - they should all turn FALSE (on that row) except the one you just made TRUE
5. Let me know how you go.

 

[usnapoleon]

Good to hear steps 1 & 3 are giving you what you were looking for, although I should point out that step 3 (the formula) will not work properly unless step 2 (the macro) works correctly, as the formula will always find the first instance of TRUE in the row, even if there are multiple instances of TRUE.

I have no idea where all of the rest of that code comes from, or what functionality it is adding to your workbook. What I'd like you to try is this:
1. Delete all macros in your workbook.
2. Copy only the Sub Change_Check_Boxes() macro (from post #2) into an empty module your workbook
3. Right-click each checkbox on your sheet, select Assign macro, and select the Change_Check_Boxes() macro (should be the only one on the list)
4. Test it by checking various checkboxes on your sheet - they should all turn FALSE (on that row) except the one you just made TRUE
5. Let me know how you go.

Performing what you said, and I'll show you to validate this just in case I do something wrong. All macros deleted, and copied into an empty module:

Only the first row has the checkboxes linked to the cells, so I performed step #3 just to each checkbox in that row only.

To perform step 4 I first formatted the cells to 'General' so I can see them turn False. I'll fix the formatting to ;;; later after our testing is done.

When I checked one box, it turned TRUE but the others didnt turn FALSE. Was that supposed to happen?

I checked a 2nd box, and it unchecked the first one (which turned FALSE). So it's unchecking, thats good, and the Dept column is updating:

I checked the 4th box, and it unchecked the 2nd one, turning it false, and editing the Dept column:

I thought I would mix it up and re-check the 2nd box to see what happens. The 4th box unchecked, turned False, and the 2nd box turned True, and the Dept number updated appropriately:

So it looks like everything is accurate. It wont change any boxes to FALSE unless it was originally 'TRUE' prior, it will just leave boxes blank like we see in box #3. I think this sort of answers the question I asked earlier on this message when I started showing my test results "was that supposed to happen?" - no, checking a box will not turn all the other boxes FALSE automatically. They only change to FALSE if they were previously TRUE, otherwise they stay blank.

Everything accurate to what you were expecting? Did I mess anything up?

Things look good on my end, and if you think the same, then I'll do 2 things:

1) go to each... and every.... checkbox and go to Format Control and link it to the cell the box is sitting in. From my understanding this has to be done 1 box at a time.

2) go each ... and every... checkbox again, and assign the macro from your step #3

That should wrap it up unless I'm missing something.

 

[kevin9999]

Everything accurate to what you were expecting? Did I mess anything up?

Looks like you've got it 100% right

1) go to each... and every.... checkbox and go to Format Control and link it to the cell the box is sitting in. From my understanding this has to be done 1 box at a time.

Correct

2) go each ... and every... checkbox again, and assign the macro from your step #3

Correct

That should wrap it up unless I'm missing something.

I don't think you've missed anything - well done!