Looping through Check boxes?

[Royalbloodi]

I don't even know how to search what I'm requesting so I started a thread. I apologize if this has been answered.

So, I have a form for accounting. I want to have check boxes for if the buyer is paying for shipping. When the check box is checked, it will do one formula and produce the profit amount in Cell S8. If it's not checked, it will do another formula and produce the profit amount in Cell S8. I would like to reduce the amount code I need because I plan on having 50 check boxes that need to be addressed.

So, the columns are:

P = Price the item was sold for
N = Quantity sold
J = Unit Cost
R = Cost of shipping
S = Profit amount

My Check Boxes are all in line Q (ActiveX)

Currently I only have this:

Private Sub CheckBox1_Click()

If CheckBox1.Value Then
    'Checked
    Range("S8").Formula = "=(P8*N8)-(N8*J8)"
  Else
    'Unchecked
    Range("S8").Formula = "=(P8*N8)-(N8*J8)-R8"
  End If

End Sub

This handles 1 Check Box. I really don't want to do this for 50 of them...

 

[Micron]

I kinda know what you need but unfortunately I never learned class modules. If it helps, look for code examples using search keywords like With Events. Or use search terms such as "excel vba link event to multiple objects". Such a class module can run the same event for the same type of "thing" such as a textbox, checkbox, or often as a result of a user action. However, it will not distinguish between one sheet or another should you only want this to apply to certain controls. If your wb or sheet has checkboxes that you don't want to be included, then you will need a way to separate them. That could be via sheet name. If only one sheet contains checkboxes but you want to separate some checkboxes from others, one way would be to use the Tag property (if the control has that property) and another would be how you name them (e.g checkbox1 through checkbox50 ) so your code would check the number at the end and do or don't do anything (or check the sheet name) as required.

Doing something like modifying control names or properties can be done via code so that you don't have to do it manually. For example, this should loop through all ActiveX checkboxes on one sheet and set their enabled property to false:

Private Sub CheckBox1_Click()
Dim obj As Object

For Each obj In Me.OLEObjects
    If TypeName(obj.Object) = "CheckBox" Then
        obj.Enabled = True
    End If
Next

End Sub

HTH

 

[Alex Blakenburg]

If you are using MS365 do you really need VBA for this ?
Can you use the built in CheckBox feature ?