Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A quick way to edit the formula in check boxes
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A quick way to edit the formula in check boxes
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. Plum wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a Formula Contingent on Check Boxes | Excel Worksheet Functions | |||
Formula for using check boxes | New Users to Excel | |||
Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) | |||
How do i create a value for check boxes or option boxes | Excel Discussion (Misc queries) | |||
Creating Formula using check boxes | Excel Discussion (Misc queries) |