Change Option Buttons Based on Checkboxes in Userform

**SoothSailor** · 10-22-2014, 10:27 AM

Hey all,
So here's what I already have coded successfully:

In Frame 1 I have option buttons for:

All Days
Weekdays
Other

and in Frame 2 I have Checkboxes for

Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

So far, I've made it so that when you click the "All Days" option, checks in the Monday - Sunday boxes automatically appear
when you click the "Weekdays" option, checks in the Monday - Friday boxes automatically appear
when you click the "Other" option, all checkboxes are set to false so the user can select individual days.

What I need, is for the option buttons to change with the checkboxes. So if the user clicks "Other" but checks the Monday - Friday boxes, then the "Weekdays" option would switch to "true". If all of the days are checked, the "All days" option button would be set to "True". If any combination of days other than Mon-Fri and Mon-Sun are checked, then the "Other" option would be set to "True".

Essentially, the Option buttons change the check boxes only. I need the Option buttons to change the check boxes and the check boxes to change the Option buttons.

Here's the code so far:
(option buttons are OptAllDays, OptWeekdays, and OptOther)

Thank you!

Private Sub OptAllDays_Click()
If OptAllDays = True Then

Me.CbMonday = True
Me.CbTuesday = True
Me.CbWednesday = True
Me.CbThursday = True
Me.CbFriday = True
Me.CbSaturday = True
Me.CbSunday = True
Else
Me.CbMonday = False
Me.CbTuesday = False
Me.CbWednesday = False
Me.CbThursday = False
Me.CbFriday = False
Me.CbSaturday = False
Me.CbSunday = False
Me.CbSaturday = False
Me.CbSunday = False
End If
End Sub

Private Sub OptWeekdays_Click()
If OptWeekdays = True Then

Me.CbMonday = True
Me.CbTuesday = True
Me.CbWednesday = True
Me.CbThursday = True
Me.CbFriday = True
Me.CbSaturday = True
Me.CbSunday = True
Me.CbSaturday = False
Me.CbSunday = False
End If
End Sub

Private Sub OptOther_Click()
If OptOther = True Then
Me.CbMonday = False
Me.CbTuesday = False
Me.CbWednesday = False
Me.CbThursday = False
Me.CbFriday = False
Me.CbSaturday = False
Me.CbSunday = False
Me.CbSaturday = False
Me.CbSunday = False
End If
End Sub

**LJMetzger** · 10-22-2014, 03:12 PM

Hi SoothSailor,

There are two issues we have to solve:
a. Getting the logic correct.
b. Preventing the CheckBoxes/OptionButtons from cross-pollinating each other. The cross-pollination problem is the reason for the Global boolean variable.

By definition, CheckBoxes are independent and don't need to be in a Frame. The OptionButtons can be in a Frame or they can be in the same Group. The Group Property can be assigned in the 'Properties' Window by assigning all the OptionButtons in the group to the same Group Name.

Lewis

Try replacing your UserForm code that you posted with the following (tested and working):

Option Explicit

Private bDisableUserFormEvents As Boolean     'Initializes to False


Private Sub cbMonday_Click()
  If bDisableUserFormEvents = False Then
    Call ProcessDayOfWeekCheckBoxButtonClick
  End If
End Sub


Private Sub cbTuesday_Click()
  If bDisableUserFormEvents = False Then
    Call ProcessDayOfWeekCheckBoxButtonClick
  End If
End Sub

Private Sub cbWednesday_Click()
  If bDisableUserFormEvents = False Then
    Call ProcessDayOfWeekCheckBoxButtonClick
  End If
End Sub

Private Sub cbThursday_Click()
  If bDisableUserFormEvents = False Then
    Call ProcessDayOfWeekCheckBoxButtonClick
  End If
End Sub

Private Sub cbFriday_Click()
  If bDisableUserFormEvents = False Then
    Call ProcessDayOfWeekCheckBoxButtonClick
  End If
End Sub

Private Sub cbSaturday_Click()
  If bDisableUserFormEvents = False Then
    Call ProcessDayOfWeekCheckBoxButtonClick
  End If
End Sub

Private Sub cbSunday_Click()
  If bDisableUserFormEvents = False Then
    bDisableUserFormEvents = True
    Call ProcessDayOfWeekCheckBoxButtonClick
    bDisableUserFormEvents = False
  End If
End Sub

Private Sub OptAllDays_Click()

If bDisableUserFormEvents = False Then

  'Disable UserForm Events When CheckBoxes are selected
  bDisableUserFormEvents = True
    
  If OptAllDays = True Then
    Me.cbMonday = True
    Me.cbTuesday = True
    Me.cbWednesday = True
    Me.cbThursday = True
    Me.cbFriday = True
    Me.cbSaturday = True
    Me.cbSunday = True
  End If
    
  'Enable UserForm Events When CheckBoxes are selected
  bDisableUserFormEvents = False
End If

End Sub

Private Sub OptWeekdays_Click()

If bDisableUserFormEvents = False Then

  'Disable UserForm Events When CheckBoxes are selected
  bDisableUserFormEvents = True
    
  If OptWeekdays = True Then
    Me.cbMonday = True
    Me.cbTuesday = True
    Me.cbWednesday = True
    Me.cbThursday = True
    Me.cbFriday = True
    Me.cbSaturday = True
    Me.cbSunday = True
    Me.cbSaturday = False
    Me.cbSunday = False
  End If
  
  'Enable UserForm Events When CheckBoxes are selected
  bDisableUserFormEvents = False
End If

End Sub

Private Sub OptOther_Click()

If bDisableUserFormEvents = False Then

  'Disable UserForm Events When CheckBoxes are selected
  bDisableUserFormEvents = True
    
  If OptOther = True Then
    Me.cbMonday = False
    Me.cbTuesday = False
    Me.cbWednesday = False
    Me.cbThursday = False
    Me.cbFriday = False
    Me.cbSaturday = False
    Me.cbSunday = False
    Me.cbSaturday = False
    Me.cbSaturday = False
  End If
  
  'Enable UserForm Events When CheckBoxes are selected
  bDisableUserFormEvents = False
End If

End Sub

Private Sub ProcessDayOfWeekCheckBoxButtonClick()

  Dim iCount As Integer
  Dim bHaveAllWeekDaysOnly As Boolean
  
  'Disable UserForm Events when Option Buttons are selected
  bDisableUserFormEvents = True
  
  If Me.cbMonday = True Then
    iCount = iCount + 1
  End If

  If Me.cbTuesday = True Then
    iCount = iCount + 1
  End If

  If Me.cbWednesday = True Then
    iCount = iCount + 1
  End If

  If Me.cbThursday = True Then
    iCount = iCount + 1
  End If

  If Me.cbFriday = True Then
    iCount = iCount + 1
  End If
  
  If iCount = 5 Then
    bHaveAllWeekDaysOnly = True
  End If

  If Me.cbSaturday = True Then
    bHaveAllWeekDaysOnly = False
    iCount = iCount + 1
  End If

  If Me.cbSunday = True Then
    bHaveAllWeekDaysOnly = False
    iCount = iCount + 1
  End If

  If iCount = 7 Then
    Me.OptAllDays = True
  ElseIf bHaveAllWeekDaysOnly = True Then
    Me.OptWeekdays = True
  Else
    Me.OptOther = True
  End If


  'Reenable Option Button Events
  bDisableUserFormEvents = False
  
End Sub