Hi, I have a list of 1,000 check boxes that all need their cell links
linking to the cell underneath them.

I've done my research and found that there's no way to manually
autofill them, but that there is a macro to do the job
(http://groups.google.com/group/micro...ca2cab5338566f)

Problem is, I don't have the faintest about how to use a macro.

If anybody would like to give me a quick idiot's guide to this, it will
same me a rather repetitive night of adjusting cell links...!!

Thanks!
Nick

First, that routine actually adds new checkboxes from the Forms toolbar to a
sheet--it doesn't modify any existing checkbox properties.

If your checkboxes are from the Forms toolbar, you can use this macro instead:

Option Explicit
Sub FormsCBX()

Dim myCBX As CheckBox
Dim wks As Worksheet

Set wks = ActiveSheet

For Each myCBX In ActiveSheet.CheckBoxes
With myCBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next myCBX

End Sub

If your checkboxes are from the Control toolbox toolbar, you can use this macro
instead:

Option Explicit
Sub CtrlCBX()

Dim OLEObj As OLEObject
Dim wks As Worksheet

Set wks = ActiveSheet

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

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel to test it out.

Tools|Macro|macros|select that macro and click Run

Save your workbook before you run the macro--then if you have trouble, you can
close without saving and bring things back the way they were.

wrote:

Hi, I have a list of 1,000 check boxes that all need their cell links
linking to the cell underneath them.

I've done my research and found that there's no way to manually
autofill them, but that there is a macro to do the job
(http://groups.google.com/group/micro...ca2cab5338566f)

Problem is, I don't have the faintest about how to use a macro.

If anybody would like to give me a quick idiot's guide to this, it will
same me a rather repetitive night of adjusting cell links...!!

Thanks!
Nick

--

Dave Peterson