Mandatory tickboxes and option selections?

[ladyath]

Hi
How do I tell excel to use mandatory checkboxes and option fields? I have a form where users need to check either options (single selection) or checkboxes (one or more), but I cannot figure out how to set the VBA to make those fields compulsory. I have mandatory text fields and I use the following code for that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Range, j As Range, k As Range, l As Range, m As Range, n As Range, o As Range, p As Range, q As Range
    Set i = Sheets("RFC").Range("J3")
    Set j = Sheets("RFC").Range("C4")
    Set k = Sheets("RFC").Range("C5")
    Set l = Sheets("RFC").Range("C6")
    Set m = Sheets("RFC").Range("C7")
    Set n = Sheets("RFC").Range("C15")
    Set o = Sheets("RFC").Range("C17")
    Set p = Sheets("RFC").Range("C18")
    Set q = Sheets("RFC").Range("C19")
          
    If i.Value = "" Then
        i.Select
        GoTo cancelMe
    End If
     
    If j.Value = "" Then
        j.Select
        GoTo cancelMe
    End If
     
    If k.Value = "" Then
        k.Select
        GoTo cancelMe
    End If
     
    If l.Value = "" Then
        l.Select
        GoTo cancelMe
    End If
     
    If m.Value = "" Then
        m.Select
        GoTo cancelMe
    End If
     
    If n.Value = "" Then
        n.Select
        GoTo cancelMe
    End If
     
    If o.Value = "" Then
        o.Select
        GoTo cancelMe
    End If
     
    If p.Value = "" Then
        p.Select
        GoTo cancelMe
    End If
     
    If q.Value = "" Then
        q.Select
        GoTo cancelMe
    End If
    
    Exit Sub
cancelMe:
    MsgBox "Please complete all mandatory fields."
    Cancel = True 'cancels the save event
     
End Sub

On my worksheet, J5 would contain a group (grp1) of 7 checkboxes of which at least one needs to be ticked. C9 has an choice of two options in one field, also grouped (grp2). C10 has a field of 5 options of which one needs to be ticked (grp3), etc. How would I add those to the mandatory text code above?

 

[Nalani]

On my worksheet, J5 would contain a group (grp1) of 7 checkboxes of which at least one needs to be ticked.

I'm not quite clear on what you are actually trying to do on your worksheet.

You cannot Group CheckBoxes to act as one, but you can do it with Option Buttons.

From HELP:

Option button Allows a single choice from a limited set of mutually exclusive choices. An option button (or radio button) is usually contained in a group box or frame. For example, you can use an option button on an order form so that a user can select one of a range of sizes, such as small, medium, large, or extra large.

Check box Turns on or off a value that indicates an opposite and unambiguous choice. You can select more than one check box at a time on a worksheet or in a group box.

Also as a helpful suggestion, the way your code is currently written, the cursor will go to the fist blank cell after testing for "" regardless of if there are other cells with "". i.e if C5 is blank and C17:C19 are also blank, your cursor ends up at C5.

Another way of writing the same is this:

   If i.Value = "" Or j.Value = "" Or k.Value = "" Or l.Value = "" Or m.Value = "" _
                Or n.Value = "" Or o.Value = "" Or p.Value = "" Or q.Value = "" Then
        i.Select   [COLOR=red][B]'change to where you want the cursor to go[/B][/COLOR]
        GoTo cancelMe
    End If

 

[ladyath]

So I need to have checkboxes in individual cells if they cannot be grouped? Or can they still be in one cell?

What would be code to check if one or more of a selection of checkboxes are ticked or not?
What would be the code to check if an option in a group is checked or not?

I need to add the validation to my spreadsheet to prevent saving unless users made all their selections.