How do I change the value of a single cell based on the input of at least 2 checkboxes?

[DreyFox]

I'm trying to link two checkboxes together, like so in the image below. For example, when I hit the first checkbox and then, the second checkbox, the cell value should be 17.64. Then when I hit the second checkbox and then the first, the cell value should still be 17.64. However for some reason, the cell value sets itself to 7.53 when doing the second way. Here is what I am attempting to do.

The first checkbox on its own gives this value:

The first, then the second should give this value:

The second, then first should also give the same value as above, but does not:

Here is the code for both checkboxes. Any help would be greatly appreciated.

The first checkbox (top):

If CheckBox22.Value = True And CheckBox17.Value = True Then
    Range("I14").Value = IIf(Me.CheckBox22, "15.06", "")
ElseIf CheckBox22.Value = True Then
    Range("I14").Value = IIf(Me.CheckBox22, "7.53", "")
ElseIf CheckBox22.Value = True And CheckBox23.Value = True Then
    Range("I14").Value = IIf(Me.CheckBox22, "17.64", "")
End If

The second checkbox (bottom):

If CheckBox23.Value = True And CheckBox22.Value = True Then
    Range("I14").Value = IIf(Me.CheckBox23, "17.64", "")
End If

 

[6StringJazzer]

You are showing two checkboxes but your code identifies three (17, 22, 23). Which checkbox is which?

If the code is not giving you the right results it is probably not the best way to describe the logic you want to use. Can you describe the overall logic in a truth table like this:

Checkbox 1	Checkbox 2	Value in I14
Checked	Checked	17.64?
Checked	Unchecked	7.53?
Unchecked	Checked	??
Unchecked	Unchecked	??

 

[Fluff]

With your 1st checkbox you have

ElseIf CheckBox22.Value = True Then

If 22 is checked it will never move to this line

ElseIf CheckBox22.Value = True And CheckBox23.Value = True Then

hence you get the wrong value.

Also as the code is checking to see if 22 is checked this line

Range("I14").Value = IIf(Me.CheckBox22, "15.06", "")

can simply become

Range("I14").Value = "15.06"

 

[DreyFox]

You are showing two checkboxes but your code identifies three (17, 22, 23). Which checkbox is which?

If the code is not giving you the right results it is probably not the best way to describe the logic you want to use. Can you describe the overall logic in a truth table like this:

Checkbox 1	Checkbox 2	Value in I14
Checked	Checked	17.64?
Checked	Unchecked	7.53?
Unchecked	Checked	??
Unchecked	Unchecked	??

Sorry yes that was a terrible explanation. Table is shown below:

CheckBox1	CheckBox2	Value in I14
Checked	Checked	17.64
Checked	Unchecked	7.53
Unchecked	Checked	10.11
Unchecked	Unchecked	No value/ blank

 

[Fluff]

How about

If CheckBox1.Value = True And CheckBox2.Value = True Then
    Range("I14").Value = 17.64
ElseIf CheckBox1.Value = True Then
    Range("I14").Value = 7.53
ElseIf CheckBox2.Value = True Then
    Range("I14").Value = 10.11
Else
    Range("I14").Value = ""
End If

 

[DreyFox]

How about

If CheckBox1.Value = True And CheckBox2.Value = True Then
    Range("I14").Value = 17.64
ElseIf CheckBox1.Value = True Then
    Range("I14").Value = 7.53
ElseIf CheckBox2.Value = True Then
    Range("I14").Value = 10.11
Else
    Range("I14").Value = ""
End If

Would this go in CheckBox1_Click() or CheckBox2_Click() or both?

 

[Fluff]

It would need to go in both.

 

[6StringJazzer]

Yes, that is an important point--when you are implementing logic based on which combination of checkboxes are checked, the same logic has to execute every time either box changes. I would write a new sub with @Fluff's code and call it from both Click events subs. Then if you need to change it you only have to change it in one place.

Private Sub CheckBox1_Click()
   SetValue
End Sub

Private Sub CheckBox2_Click()
   SetValue
End Sub

Private Sub SetValue()

   If CheckBox1.Value = True And CheckBox2.Value = True Then
       Range("I14").Value = 17.64
   ElseIf CheckBox1.Value = True Then
       Range("I14").Value = 7.53
   ElseIf CheckBox2.Value = True Then
       Range("I14").Value = 10.11
   Else
       Range("I14").Value = ""
   End If

End Sub

 

[DreyFox]

Yes, that is an important point--when you are implementing logic based on which combination of checkboxes are checked, the same logic has to execute every time either box changes. I would write a new sub with @Fluff's code and call it from both Click events subs. Then if you need to change it you only have to change it in one place.

Private Sub CheckBox1_Click()
   SetValue
End Sub

Private Sub CheckBox2_Click()
   SetValue
End Sub

Private Sub SetValue()

   If CheckBox1.Value = True And CheckBox2.Value = True Then
       Range("I14").Value = 17.64
   ElseIf CheckBox1.Value = True Then
       Range("I14").Value = 7.53
   ElseIf CheckBox2.Value = True Then
       Range("I14").Value = 10.11
   Else
       Range("I14").Value = ""
   End If

End Sub

Would this be easier than Fluff's method if I have say 30 checkboxes?

 

[Fluff]

You would need to create a new macro for each group of checkboxes, whether you consider it easier to have one macro for each group, or the individual code in each checkbox, only you can say.