excelvbacheckboxrangeformula

Follow up to Using ActiveX Checkboxes Conditions to check or disable another Checkbox that results in Formula

So this is a follow up to a previous question. That was answered wonderfully. However now I have a problem where I added another column and have specific formulas needed depending on certain checkboxes. It should be a quick edit but I keep getting an error and not sure what is wrong

Using ActiveX Checkboxes Conditions to Check or Disable Another Checkbox AND to Populate Column

This is in VBA for excel.

I have slightly different conditions so here is the new table

CheckBox 1 CheckBox 2 Checkbox 3 Results 1 Results 2
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3
A4 B4 C4 D4 E4
A5 B5 C5

So if Checkbox 1 is clicked checkbox 2 is disabled and Check box 3 can still be clicked

If checkbox 2 is clicked Checkbox 1 is enabled but check box 3 can still be enabled

If checkbox 3 is clicked then both Checkbox 1 and 2 can still be enabled

There is an additional check box check box 4 that will disabled if any one of the checkboxes are clicked but will remain clicked if checkbox 1 - 3 are not clicked

If checkbox 1 is the only one clicked then Results 1 column (D2:D3) becomes cell value in A1-A3 multiplied by A5 and together that divided by constant 100

And results 2 is a constant (100) subtracted by the value in cells A2 - A4

Same thing for checkbox 2 and 3 for columns B and C respectively

Now if Checckbox 1 and 3 get clicked Results 1 are what would have been results of checkbox 1 in addition to checkbox 3

And the result in D2 the answer would be ((A2 * A5) + (C2 * C5))/ 100 and so on

Results E2 would be 100 - (A2+C2) and so on

Same thing for if checkbox 2 and 3 get clicked, only with cells so for the result in D2 the answer would be ((B2 * B5) + (C2 * C5))/100 and so on

Results E2 would be 100 - (B2+C2) and so on

I've tried to use various combination trying to get it to do the formula for the first cell and then move onto the second cell but it's just not working and the way I have want to. Whatever I do, I seem to get an an error message.

I know in general steps correct. I have a similar scenario where there was just a two cells in each column A through C and looking for one result in Columns D and E. This code works fine for that set up. But I just can't get it to work with an actual range.

Sub chkbox1_Click()

If chkbox1.Value = False And chkbox2.Value = False And chkbox3.Value = False Then
    chkbox4.Value = True
    chkbox4.Enabled = True
    chkbox1.Enabled = True
    chkbox2.Enabled = True
    chkbox3.Enabled = True
    range(D2:D4).Value = 0
    range(E2:E3).Value = 100
    
ElseIf chkbox1.Value = False And chkbox3.Value = True Then
    chkbox2.Enabled = True
    chkbox2.Enabled = True
    chkbox4.Value = False
    chkbox4.Enabled = False
    range(D2:D4).Formula= (range(C2:C3).Value * range(C5).Value) / 100
    range(E2:E3).Formula = 100 - range(C2:C3).Value
    
ElseIf chkbox1.Value = True And chkbox3.Value = False Then
    chkbox2.Value = False
    chkbox2.Enabled = False
    chkbox3.Enabled = True
    chkbox4.Value = False
    chkbox4.Enabled = False
    chkbox3.Enabled = True
    range(D2:D4).Formula= (range(A2:A3).Value * range(A5).Value) / 100
    range(E2:E3).Formula = 100 - range(A2:A3).Value
    
ElseIf chkbox1.Value = True And chkbox3.Value = True Then
    chkbox2.Value = False
    chkbox2.Enabled = False
    chkbox4.Value = False
    chkbox4.Enabled = False
   range(D2:D4).Formula= ((range(A2:A3).Value * range(A5).Value) + (range(C2:C3).Value * range(C5).Value)) / 100
    range(E2:E3).Formula = 100 - (range(A2:A3).Value + range(C2:C3).Value)
    
Else
    chkbox2.Enabled = True

End If
End Sub

Sub chkbox2_Click()

If chkbox2.Value = False And chkbox1.Value = False And chkbox3.Value = False Then
    chkbox4.Value = True
    chkbox4.Enabled = True
    chkbox1.Enabled = True
    chkbox1.Enabled = True
    chkbox3.Enabled = True
    range(D2:D4).Value = 0
    range(E2:E3).Value = 100
    
ElseIf chkbox2.Value = False And chkbox3.Value = True Then
    Chkbox1.Enabled = True
    Chkbox1.Enabled = True
    chkbox4.Value = False
    chkbox4.Enabled = False
    range(D2:D4).Formula= (range(C2:C3).Value * range(C5).Value) / 100
    range(E2:E3).Formula = 100 - range(C2:C3).Value
    
ElseIf chkbox2.Value = True And chkbox3.Value = False Then
    chkbox2.Value = False
    chkbox2.Enabled = False
    chkbox3.Enabled = True
    chkbox4.Value = False
    chkbox4.Enabled = False
    chkbox3.Enabled = True
    range(D2:D4).Formula= (range(B2:B3).Value * range(B5).Value) / 100
    range(E2:E3).Formula= 100 - range(B2:B3).Value
    
ElseIf chkbox2.Value = True And chkbox3.Value = True Then
    Chkbox1.Value = False
    Chkbox1.Enabled = False
    chkbox4.Value = False
    chkbox4.Enabled = False
   range(D2:D4).Formula= ((range(B2:B3).Value * range(B5).Value) + (range(C2:C3).Value * range(C5).Value)) / 100
    range(E2:E3).Formula= 100 - (range(B2:B3).Value + range(C2:C3).Value)
    
Else
    chkbox2.Enabled = True

End If
End Sub
Sub chkbox3_Click()

If chkbox3.Value = False And chkbox1.Value = False And chkbox2.Value = False Then
    chkbox1.Enabled = True
    chkbox2.Enabled = True
    chkbox4.Value = True
    chkbox4.Enabled = True
    chkbox1.Enabled = True
    chkbox2.Enabled = True
   range(D2:D4).Value = 0
    range(E2:E4).Value = 100

ElseIf chkbox3.Value = False And chkbox1.Value = True Then
    chkbox2.Value = False
    chkbox2.Enabled = False
    chkbox4.Value = False
    chkbox4.Enabled = False
    range(D2:D4).Formula= (range(A2:A3).Value * range(A5).Value) / 100
    range(E2:E3).Formula = 100 - range(A2:A3).Value

ElseIf chkbox3.Value = False And chkbox2.Value = True Then
    chkbox1.Value = False
    chkbox1.Enabled = False
    chkbox4.Value = False
    chkbox4.Enabled = False
    range(D2:D4).Formula= (range(B2:B3).Value * range(B5).Value) / 100
    range(E2:E3).Formula= 100 - range(B2:B3).Value

ElseIf chkbox3.Value = True And chkbox1.Value = False And chkbox2.Value = False Then
    chkbox1.Enabled = True
    chkbox2.Enabled = True
    chkbox4.Value = False
    chkbox4.Enabled = False
   range(D2:D4).Formula= (range(C2:C3).Value * range(C5).Value) / 100
    range(E2:E3).Formula = 100 - range(C2:C3).Value
  
ElseIf chkbox3.Value = True And chkbox1.Value = True Then
    chkbox2.Value = False
    chkbox2.Enabled = False
    chkbox4.Value = False
    chkbox4.Enabled = False
   range(D2:D4).Formula= ((range(A2:A3).Value * range(A5).Value) + (range(C2:C3).Value * range(C5).Value)) / 100
    range(E2:E3).Formula = 100 - (range(A2:A3).Value + range(C2:C3).Value)    

ElseIf chkbox3.Value = True And chkbox2.Value = True Then
    chkbox1.Value = False
    chkbox1.Enabled = False
    chkbox4.Value = False
    chkbox4.Enabled = False
    range(D2:D4).Formula= (range(B2:B3).Value * range(B5).Value) / 100
    range(E2:E3).Formula= 100 - range(B2:B3).Value

Else:
    chkbox1.Enabled = True
    chkbox2.Enabled = True

End If
End Sub

Thank you in advance for your help.

Solution 
  • Option Explicit

Sub chkbox1_Click()
    If chkbox1 Then
        chkbox2.Enabled = False
    Else
        chkbox2.Enabled = True
    End If
    Call SetFormula
End Sub

Sub chkbox2_Click()
    If chkbox2 Then
        chkbox1.Enabled = False
    Else
        chkbox1.Enabled = True
    End If
    Call SetFormula
End Sub

Sub chkbox3_Click()
    Call SetFormula
End Sub

Sub SetFormula()
    Dim f1 As String, f2 As String
    
    If chkbox1 Then
        If Not chkbox3 Then
            f1 = "=A2*$A$5/100"
            f2 = "=100-A2"
        Else
            f1 = "=((A2*$A$5) + (C2*$C$5))/100"
            f2 = "=100-(A2+C2)"
        End If
    ElseIf chkbox2 Then
         If Not chkbox3 Then
            f1 = "=B2*$B$5/100"
            f2 = "=100-B2"
        Else
            f1 = "=((B2*$B$5) + (C2*$C$5))/100"
            f2 = "=100-(B2+C2)"
        End If
    ElseIf chkbox3 Then
        f1 = "=C2*$C$5/100"
        f2 = "=100-C2"
    Else
        f1 = "=0"
        f2 = "=100"
    End If
    
    Range("D2:D4").Formula = f1
    Range("E2:E4").Formula = f2
    MsgBox "Formula set " & vbLf & f1 & vbLf & f2, vbInformation
    
End Sub