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

DreyFox

New Member
Joined
Nov 25, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
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:
1608063115887.png

The first, then the second should give this value:
1608121006542.png

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

1608121067268.png

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

The first checkbox (top):
VBA Code:
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):
VBA Code:
If CheckBox23.Value = True And CheckBox22.Value = True Then
    Range("I14").Value = IIf(Me.CheckBox23, "17.64", "")
End If
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
862
Office Version
  1. 365
Platform
  1. Windows
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 1Checkbox 2Value in I14
CheckedChecked17.64?
CheckedUnchecked7.53?
UncheckedChecked??
UncheckedUnchecked??
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,677
Office Version
  1. 365
Platform
  1. Windows
With your 1st checkbox you have
VBA Code:
ElseIf CheckBox22.Value = True Then
If 22 is checked it will never move to this line
VBA Code:
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
VBA Code:
Range("I14").Value = IIf(Me.CheckBox22, "15.06", "")
can simply become
VBA Code:
Range("I14").Value = "15.06"
 

DreyFox

New Member
Joined
Nov 25, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
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 1Checkbox 2Value in I14
CheckedChecked17.64?
CheckedUnchecked7.53?
UncheckedChecked??
UncheckedUnchecked??
Sorry yes that was a terrible explanation. Table is shown below:
CheckBox1CheckBox2Value in I14
Checked Checked17.64
Checked Unchecked7.53
UncheckedChecked10.11
UncheckedUncheckedNo value/ blank
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,677
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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
 
Solution

DreyFox

New Member
Joined
Nov 25, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,677
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It would need to go in both.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
862
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Nov 25, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,677
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,828
Messages
5,661,160
Members
418,619
Latest member
ibruzzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top