CODEWITHSUNDEEP
excelvba
methuselah
methuselah

Reputation: 13216

Validating groups of checkboxes and radio buttons using VBA

I have the following groups of check-boxes and radio groups and was wondering how I would validate them each separately in VBA. Can anybody point me in the right direction?

For example I would require that at least checkbox is selected in the weeks group and each of the radio button groups (priority, lecturestyle, roomstruc and roomtype) just require a selection to be made. I would appreciate any code to show me how to accomplish this.

weeks - checkboxes

chk_week1, chk_week2, chk_week3, chk_week4, chk_week5,..., chk_week15

priority - radio buttons

priority_y, priority_n

lecturestyle - radio buttons

lecturestyle_trad, lecturestyle_sem

roomstruc - radio buttons

roomstruc_tiered, roomstruc_flat

roomtype - radio buttons

roomtype_lecture, roomtype_lab

Upvotes: 0

Views: 5273

Answers (2)

EssoExplJoe
EssoExplJoe

Reputation: 11

You might try something like the below coding as it gets tedious when you have many controls in group:

  1. Insert a userform named “MyForm with checkboxes named “chk01”, “chk02”….”chk20 or however many checkboxes you want

  2. Put the following declaration in a code module (not the form module)

    Public CheckBoxGroup(1 To 20) As New MyForm

  3. Put the following declarations in the form code

    Public WithEvents ChkBoxGrp As CheckBox

  4. Initialize the array in the form activate event

    Private Sub UserForm_Activate()
Dim i As Integer
    For i = 1 To 20 (or however many checkboxes you have
            set CheckBoxGroup (i).ChkBoxGrp = MyForm("chk" + Right("0" + CStr(i), 2))
    Next i
End Sub

Private Sub ChkBoxGrp_Click()
    Select Case val(Right(ChkBoxGrp.Name, 2))
            Case 1
            ‘Do whaever you want for chk01 box
        Case 2
            ‘Do whaever you want for chk01 box
        ……………
        End Select
End Sub

You can use the same event handling for other checkbox events

Upvotes: 1

nutsch
nutsch

Reputation: 5962

You can try something like this. Naming a frame priority could return weird results so I have renamed your frames frPriority and frLectureStyle

Sub cbValidate_Click()
Dim ctl As Control, strOption As String, lLoop As Long

For lLoop = 1 To 15
    If Me.Controls("chk_week" & lLoop).Value = True Then GoTo nxtCheck0
Next

MsgBox "You didn't select a week"

nxtCheck0:

For Each ctl In Me.frPriority.Controls
    If ctl.Value = True Then GoTo nxtCheck1
Next

MsgBox "You didn't select a priority"

nxtCheck1:

For Each ctl In Me.frLectureStyle.Controls
    If ctl.Value = True Then GoTo nxtCheck1
Next

MsgBox "You didn't select a lecture Style"

nxtCheck1:

Unload Me
End Sub

Upvotes: 1

Related Questions