Check Box or Data Validation Error Message based on a formula

[bnichols]

Hello, trying to get a data validation, or more specifically, a warning message (and a stop message) based on another cells value once the check box is selected. The only way it will work is if the cell is edited, which is how data validation works but I'm trying to automate, VBA would be useful also. Essentially, im trying to have an "is this done" check list and once the box is checked, it will verify, for example, a certain row of cells contains entered numbers and if they don't, the user can proceed to the next section of the sheet.


View attachment 81649

 

[bnichols]

It should have picked up with .Columns(8), but wouldn't in .Columns(9). You could test using .Cells. For example, if your sample is in H24:I24 (either centered across selection or merged), you could test with something like the below to see if the correct string is returned.

Sub testmerge()

MsgBox ActiveSheet.Cells(24, 8).Value

End Sub

I dont know where im messing it up, the test code showed the correct column 8 and in a test sheet, it works without issue. There's something on the sheet causing the 400 error to keep popping up. I added it to a google drive if you wanted to take a look, otherwise, I appreciate the help to this point.

Book1.xlsm

drive.google.com

 

[bnichols]

It should have picked up with .Columns(8), but wouldn't in .Columns(9). You could test using .Cells. For example, if your sample is in H24:I24 (either centered across selection or merged), you could test with something like the below to see if the correct string is returned.

Sub testmerge()

MsgBox ActiveSheet.Cells(24, 8).Value

End Sub

I knew what the solution would be all along, and it's that im an idiot. Im still learning, and this is the first time I've messed with checkboxes. I've made the false assumption that check box name referred to either the name of the checkbox (written text) or the Alt text name. I realize now that there is a third way of labeling it once its clicked in the upper lefthand corner. 3 ways of naming a check box is a bit much if you ask me. All that said your original suggested code works without issues as im sure you suspected. I had an additional question, is there a certain code that could be added to a macro that hides/unhides cells, like if check box 1,2,3 etc are clicked then unhide rows 1 -5, otherwise message.

 

[bnichols]

For anyone that stumbles upon this thread for answers, after a lot of trial and error, I realized that VBA wasn't picking up true/false statements for the checkboxes; instead, for checked box use 1 (true) for unchecked box use -4146 (false). Im not sure if this is mac related but while running the step in tool, i came to this realization.