If Statement problem (1 Viewer)

[Eljefegeneo]

I am trying to prevent the user from checking a second checkbox when another is checked. I have five days listed, Day1, Day2, Day3, Day4 and Day5. They are selected from a list of days (or combination thereof) of days of the week. But we need only to be concerned with Sun, Mon, Tue, Wed, Thu, Fri, Sat.
They have a corresponding check boxBP2, BP3, etc.

Then I have a second set of seven checkboxes to update the number of times a day appears in a certain month. They are cb1, cb2, etc.
What I have written is as follows:

  Private Sub cb1_Click()
  'Prevents the day from being used in a calculation
  If (BP2 = True Or BP3 = True Or BP4 = True Or BP5 = True) And (Day2 = "Sun" Or Day3 = "Sun" Or Day4 = "Sun" Or Day5 = "Sun") Then
  cb1 = False
  MsgBox "This is a bonus day!"
  End If
   
  End Sub

I have done this for each of the seven set of checkboxes, altering the code to the day of the week.

The problem arises when I have Day2 as Mon ticked in the first set of checkboxes and have the corresponding check box BP2 ticked, but Day3 may be listed as "Tue", then when I click on cb3 which corresponds to Tuesday, it tells me that this is also a bonus day.

I realize that I do not have the correct syntax in the IF statement, but after two hours of trying all kinds of alternatives, I cannot see the trees because of the forest.

 

[Minty]

If the options are mutually exclusive (Only one can be selected) why not use an option group?

 

[Eljefegeneo]

Thank you for your reply. I to had thought of that but unfortunately this will not work in my situation. I have tried a few other combinations of ifs with ors and ands, but nothing seems to work. The problem seems to be that there are to many possible combinations in the Day1, Day2, etc. for me to figure out what to do.

I will try to post a sample DB of my problem in hopes someone can figure out how to do this.

 

[Eljefegeneo]

Figured it out on my own. Had to think about it for a day or so, the problem was with the logic of the problem. Finally decided that the cb1 should be the controlling check box and wrote the following, not pretty, but it works.

If cb1 = True And (Day2 = "Sun" And BP2 = True) Then
MsgBox "Bonus"
cb1 = False
ElseIf cb1 = True And (Day3 = "Sun" And BP3 = True) Then
MsgBox "Bonus1"
cb1 = False
ElseIf cb1 = True And (Day4 = "Sun" And BP4 = True) Then
MsgBox "Bonus2"
cb1 = False
ElseIf cb1 = True And (Day5 = "Sun" And BP5 = True) Then
MsgBox "Bonus3"
cb1 = False
End If

Still wondering if there is a way to do this with a loop.

 

[Minty]

I'm sure you could reduce this further by checking for cb1 and it being a Sunday at the start. Then use a case statement?