How to use a Checked Control Box in a formula

Joined
Jul 7, 2011
Messages
2
Reaction score
0
This is an product order form (simplified for this post). There are 150 lines. In Column A, I have a check box (tick or no tick), in column D I have a fixed Price. In Column E I have set % Disc. If column A is checked I want Column F to show the price less the discount else Blank. I started out entering each line using the controlbox tool by entering a command for each row.
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Range("aa1") = "X"
ElseIf CheckBox = False Then
Range("aa1") = ""
End Sub
After repeating this up to checkbox26 I thought "there must be an easier way". I know very little. I would like to keep the "click of a mouse" option with the checkbox column. Any ideas or suggestion would be appreciated. Thank you
 
Sample VBA Code for your problem is given below:

Code:
Private Sub CheckBox1_Click()
Process 1
End Sub

Private Sub CheckBox2_Click()
Process 2
End Sub

Private Sub CheckBox3_Click()
Process 3
End Sub

Private Function Process(ByVal intBoxNo As Integer)
Dim strBox As String, boxval As Boolean
strBox = "CheckBox" & intBoxNo
boxval = Controls(strBox)
If boxval Then
  Range("AA1").Value = "X"
Else
  Range("AA1").Value = ""
End If

End Function
A Common Function Process() is created to refer to the CheckBox control with the Number passed to the Function from the CheckBox Click Event Procedure and validates the status of the checkbox and executes the code within the IF...THEN statement.
 
Back
Top