# Check Box or Data Validation Error Message based on a formula



## bnichols (Dec 28, 2022)

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


----------



## breynolds0431 (Dec 28, 2022)

Hi there. Not sure what the attachment was meant to be (goes to an error page when clicked), but the below will work with an ActiveX checkbox named CheckBox1. You could also add some additional safeguards that would clear the checkbox if a change was made within the specified range where the checkbox would become unchecked. This would just require the user to reverify the data. See below for an example of that. 

*CheckBox1 Code:*


```
Private Sub CheckBox1_Click()

'The below uses an ActiveX checkbox (CheckBox1) on the sheet
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet

'update the below range as needed
Dim rng As Range: Set rng = ws.Range("D4:K8")

'creates a loop through rng
Dim c As Range

'will abort if checkbox is not checked
If CheckBox1.Value = False Then Exit Sub

'reviews each cell in rng (identified above) for a blank and returns
'message with cell's address if true
For Each c In rng.Cells
    If c.Value = vbNullString Then
        'message to display if blank found (includes cell address)
        MsgBox "Error!" & vbCrLf & vbCrLf & "You must input a " & _
            "number in cell " & c.Address & " before continuing." _
            , vbCritical, "Incomplete"
        'unchecks check box if a blank found
        CheckBox1.Value = False
        'stops loop/sub
        Exit Sub
    End If
Next c

End Sub
```

*Worksheet_Change Code:*
This code must be in the sheet module.


```
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Dim rng As Range: Set rng = ws.Range("D4:K8")

If Not Application.Intersect(rng, Range(Target.Address)) _
           Is Nothing Then
    CheckBox1.Value = False
End If
End Sub
```


----------



## bnichols (Dec 28, 2022)

breynolds0431 said:


> Hi there. Not sure what the attachment was meant to be (goes to an error page when clicked), but the below will work with an ActiveX checkbox named CheckBox1. You could also add some additional safeguards that would clear the checkbox if a change was made within the specified range where the checkbox would become unchecked. This would just require the user to reverify the data. See below for an example of that.
> 
> *CheckBox1 Code:*
> 
> ...


Thanks for the response! I probably should have specified but does this work for mac also? I dont think mac has activex, unless the checkbox in the developer section would function the same


----------



## breynolds0431 (Dec 28, 2022)

Sorry about that. I should have checked your platform. I'm assuming that the form checkbox is what macs have. If that's the case, then you just need to reference the checkbox a little differently including the sheet name.

Hopefully the below works for you. You can include both in the sheet module still. Take note in modifying the cbox and rng variables.


```
Sub checkboxclick()

'The below uses a Form Check Box (Check Box 3) on the sheet
'After drawing your checkbox, right-click and assign this
'macro to it. Also ensure the cbox variable is updated to suit.

'basic declarations
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet

'--> Modify the string to your Check Box number or exact name
Dim cbox As String: cbox = "Check Box 3"

'update the below range as needed
Dim rng As Range: Set rng = ws.Range("D4:K8")

'creates a loop through rng
Dim c As Range

'will abort if checkbox is not checked
If ws.CheckBoxes(cbox).Value = False Then Exit Sub

'reviews each cell in rng (identified above) for a blank and returns
'message with cell's address if true
For Each c In rng.Cells
    If c.Value = vbNullString Then
        MsgBox "Error!" & vbCrLf & vbCrLf & "You must input a " & _
            "number in cell " & c.Address & " before continuing." _
            , vbCritical, "Incomplete"
        ws.CheckBoxes(cbox).Value = False
        Exit Sub
    End If
Next c

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
'--> Modify rng to suit
Dim rng As Range: Set rng = ws.Range("D4:K8")
'--> Modify the string to your Check Box number or exact name
Dim cbox As String: cbox = "Check Box 3"

If Not Application.Intersect(rng, Range(Target.Address)) _
           Is Nothing Then
    ws.CheckBoxes(cbox).Value = False
End If
End Sub
```


----------



## bnichols (Dec 28, 2022)

breynolds0431 said:


> Sorry about that. I should have checked your platform. I'm assuming that the form checkbox is what macs have. If that's the case, then you just need to reference the checkbox a little differently including the sheet name.
> 
> Hopefully the below works for you. You can include both in the sheet module still. Take note in modifying the cbox and rng variables.
> 
> ...


If I copy the code as is and make a Check Box 3 to test, I get a
Run-time error '1004':

Application-defined or object-defined error

I probably did not do it correclty but I tried a few other things and couldnt get it going. I tried to upload a mini sheet but the checkboxes wouldnt copy so i just added a screenshot to see the lay out if.


----------



## breynolds0431 (Dec 29, 2022)

Hi. Let's try the below with just the first check box. I'm assuming that the Sales checkbox is your Check Box 1. In the below, I've set the range for check box 1 to be row 6, columns C through H. This code can again go into your sheet's module. Also, see attached image showing the correct module (under/inside the Microsoft Excel Objects folder), just so we're clear on that part. 

As long as this part works for the range("C6:H6") and check box 1, then I can put together something that would hopefully work for numerous check boxes. 


```
Sub checkboxclick()

'The below uses a Form Check Box (Check Box 3) on the sheet
'After drawing your checkbox, right-click and assign this
'macro to it. Also ensure the cbox variable is updated to suit.

'basic declarations
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet

'--> Modify the string to your Check Box number or exact name
Dim cbox As String: cbox = "Check Box 1"

'update the below range as needed
Dim rng As Range: Set rng = ws.Range("C6:H6")

'creates a loop through rng
Dim c As Range

'will abort if checkbox is not checked
If ws.CheckBoxes(cbox).Value = False Then Exit Sub

'reviews each cell in rng (identified above) for a blank and returns
'message with cell's address if true
For Each c In rng.Cells
    If c.Value = vbNullString Then
        MsgBox "Error!" & vbCrLf & vbCrLf & "You must input a " & _
            "number in cell " & c.Address & " before continuing." _
            , vbCritical, "Incomplete"
        ws.CheckBoxes(cbox).Value = False
        Exit Sub
    End If
Next c

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
'--> Modify rng to suit
Dim rng As Range: Set rng = ws.Range("C6:H6")
'--> Modify the string to your Check Box number or exact name
Dim cbox As String: cbox = "Check Box 1"

If Not Application.Intersect(rng, Range(Target.Address)) _
           Is Nothing Then
    ws.CheckBoxes(cbox).Value = False
End If
End Sub
```


----------



## bnichols (Dec 30, 2022)

breynolds0431 said:


> Hi. Let's try the below with just the first check box. I'm assuming that the Sales checkbox is your Check Box 1. In the below, I've set the range for check box 1 to be row 6, columns C through H. This code can again go into your sheet's module. Also, see attached image showing the correct module (under/inside the Microsoft Excel Objects folder), just so we're clear on that part.
> 
> As long as this part works for the range("C6:H6") and check box 1, then I can put together something that would hopefully work for numerous check boxes.
> 
> ...


Thanks again for your help, it makes learning a lot easier! So i messed around for a while and still couldn't get it to work on my actual sheet, so I pasted it in my test sheet, and it worked like a charm. I duplicated my actual sheet, pasted the code and adjusted the cell range, the pop up worked fine but the clear sub did not work. Taking the same code and trying it again in my main sheet and I get a VBA 400 error. The only difference between the test/the actual sheet is the actual has multiple tabs ( i made sure to paste it in the objects/sheet page), and I adjust the cell range to the actual range of I24:O24 and check box 1 to "Sales Entered?". Clearly user error, but did you happen to have an idea of what Im doing wrong?


----------



## breynolds0431 (Dec 30, 2022)

I'm fairly certain that the 400 error has to do with the check box names and the code being unable to find that name. If you right-click on the checkbox, what does it say in the name box? Check Box 1? If it does, then I'm stumped. 

I did play around with a workbook that I can share with you. All checkboxes are assigned to a single macro called `Sub checkboxclick()`. Using `Application.Caller` will identify the name of the checkbox that was clicked (i.e.; called the macro). It will then use that name to set the range for the loop. A similar approach is used for the `Worksheet_Change()` event, but instead of the checkbox names, it relies on the row labels in column B (e.g.; Sales, Online Purchases, etc.) to identify the row the change was made on and which checkbox that would need to be set to False. I added to dropbox and you should be able to download and play around with it to see if it sparks any thoughts with your actual data. DropBox link is checkboxes.xlsm.


----------



## bnichols (Dec 30, 2022)

breynolds0431 said:


> I'm fairly certain that the 400 error has to do with the check box names and the code being unable to find that name. If you right-click on the checkbox, what does it say in the name box? Check Box 1? If it does, then I'm stumped.
> 
> I did play around with a workbook that I can share with you. All checkboxes are assigned to a single macro called `Sub checkboxclick()`. Using `Application.Caller` will identify the name of the checkbox that was clicked (i.e.; called the macro). It will then use that name to set the range for the loop. A similar approach is used for the `Worksheet_Change()` event, but instead of the checkbox names, it relies on the row labels in column B (e.g.; Sales, Online Purchases, etc.) to identify the row the change was made on and which checkbox that would need to be set to False. I added to dropbox and you should be able to download and play around with it to see if it sparks any thoughts with your actual data. DropBox link is checkboxes.xlsm.
> 
> View attachment 81799


Much appreciated, im messing around with it to see if I can get it to work. How would I list ws.Columns(2) if the "sales" were in 2 columns. Meaning originally, I had it formatted as center across selection in H to I (Sales was written in H) and I also tried center across and wasn't able to get ws.Columns(8) or ws.Columns(9) to pick "sales" up. If I shift sales into column 9 I can get it to work but not with the 2.


----------



## breynolds0431 (Dec 30, 2022)

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
```


----------



## bnichols (Dec 28, 2022)

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 (Dec 30, 2022)

breynolds0431 said:


> 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.
> 
> 
> ```
> ...


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 (Dec 31, 2022)

breynolds0431 said:


> 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.
> 
> 
> ```
> ...


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 (Jan 1, 2023)

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.


----------

