A quick way to edit the formula in check boxes

[Guest]

Hi there,
I have got a massive number of check boxes which all need to be linked to a
cell. I know you can do this by clicking on each box individually, but I
wondered if anyone knows a quick way of editing the formulas behind the boxes
en masse.
If it were an ordinary formula, find and replace would do the trick but this
doesn't work for check boxes.
Thanks.

 

[Dave Peterson]

It depends on the type of checkbox--are they from the Forms toolbar or from the
Control toolbox toolbar?

And where do you want the linked cell?

I used the cell that held the checkbox as the linked cell in both these
routines:

Option Explicit
Sub testme01()
Dim CBX As CheckBox
For Each CBX In ActiveSheet.CheckBoxes
CBX.LinkedCell = CBX.TopLeftCell.Address(external:=True)
'hide the value??
CBX.TopLeftCell.NumberFormat = ";;;"
Next CBX
End Sub

Sub testme02()
Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.LinkedCell = OLEObj.TopLeftCell.Address(external:=True)
OLEObj.TopLeftCell.NumberFormat = ";;;"
End If
Next OLEObj
End Sub

The first is for checkboxes from the Forms toolbar. The second is for
checkboxes from the Control Toolbox Toolbar.