I just started learning Excel VBA Programming. Just read a book and decided to try this out.

I already added checkboxes using the VBA codes below:


Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Offset(0, 1).Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
Next
myRange.Select
End Sub


Now, I want the checkboxes to be checked (True) based on which option is chosen from a particular cell. The macro give a result of a calculation based on selected parameter and the checked boxes. If for instance i choose "Standard", i want some checkboxes to be checked and some unchecked automatically.

This is what i have so far:

'i created a commandbutton to display a result

Option Explicit

Private Sub CommandButton1_Click()
'command button property
CommandButton1.BackColor = &H8000000F
CommandButton1.Caption = "Calculate Premium"
CommandButton1.Font.Bold = True
CommandButton1.Font.Name = "Book Man Old Style"
'to change parameters bewteen 0 and 1
If Range("Plan") = "Standard" Then
Range("I12") = 1: Range("J12") = 0: Range("K12") = 0
End If
If Range("Plan") = "Executive" Then
Range("I12") = 0.5: Range("J12") = 0.5: Range("K12") = 0
End If
If Range("Plan") = "Super Executive" Then
Range("I12") = 0: Range("J12") = 1: Range("K12") = 0
End If
If Range("Plan") = "Magnum" Then
Range("I12") = 0: Range("J12") = 0.7: Range("K12") = 0.3
End If
'to obtain a result and display in another section
Range("PremiumTable").ClearContents
Range("Premium") = Range("Answer")
Range("J30") = Range("L30") * Range("Answer")
Range("I31") = 12 * Range("Answer")
Range("J31") = 12 * Range("J30")

End Sub

'I want the checkboxes selected or deselected based on some parameters

Private Sub clearcheckbox()
If Range("Plan") = "Standard" Then
CheckBox1.Value = False: CheckBox2.Value = True: CheckBox3.Value = True: CheckBox4.Value = False
ElseIf Range("Plan") = "Executive" Then
CheckBox1.Value = True: CheckBox2.Value = True: CheckBox3.Value = False: CheckBox4.Value = True
End If
End If
End Sub





i will appreciate any help. Thank you