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

 

[breynolds0431]

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]

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

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]

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]

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

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]

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]

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

View attachment 81761

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]

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]

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]

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