I have placed Check boxes from the forms toolbar in cells A1:A300

In format control I want the cell links to be say B1 for A1, B2 for A2 etc.

Is there an easy way to copy the formats down?

Thanks very much

Not really unless you are lucky. The checkboxes like all controls arren't
linked to the worksheet. The textt box location is specified in X-Pixels and
Y-Pixels. Cells on worksheets also have a Pixel location. Using a macro it
is difficult to associate a control with a cell but it can be done.

It would be easier if the names of the check boxes where is some sort of
order like checkbox 1 is on cell B1 and checkbox 2 is on cell B2 and so on.
The name of the check box can be anything as long as the numbers are in
order. A simple macro can be written to go down a list of names in order and
add the LinkedCell property. Thats why I said if you are lucky.

"Steve Jones" wrote:

I have placed Check boxes from the forms toolbar in cells A1:A300

In format control I want the cell links to be say B1 for A1, B2 for A2 etc.

Is there an easy way to copy the formats down?

Thanks very much

Are you married to the idea of using checkboxes? I find this alternative easier
to implement and work with:

(saved from a previous post)

Select the range that would have held the checkboxes.
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings (make it as large as you want)

Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:
=if(a1="","no checkmark","Yes checkmark")

Or you can filter by blanks and non-blanks.

=================
But if you really want checkboxes....

You can use a macro to do the work.

This version uses the cell containing the checkbox as the linked cell. It also
hides that value in the worksheet (but not the formulabar) by using a custom
number format of: ;;;

Option Explicit
Sub LayOutCheckboxes()

Dim myCBX As CheckBox
Dim myCell As Range
Dim wks As Worksheet
Dim iCtr As Long

Set wks = ActiveSheet

Application.ScreenUpdating = False

iCtr = 0
With wks
.CheckBoxes.Delete 'nice for testing
For Each myCell In .Range("a1:a300").Cells
With myCell
.NumberFormat = ";;;" 'hide the true/false
iCtr = iCtr + 1
If iCtr Mod 50 = 0 Then
DoEvents
Application.StatusBar _
= "Processing: " & myCell.Address(0, 0)
End If
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!CbxClick"
End With
End With
Next myCell
End With

With Application
.StatusBar = False
.ScreenUpdating = False
End With
End Sub

If you really want to use column B (one cell to the right), change this line:
..LinkedCell = myCell.Address(external:=True)
to
..LinkedCell = myCell.offset(0,1).Address(external:=True)
and delete this line:
..NumberFormat = ";;;" 'hide the true/false



If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Steve Jones wrote:

I have placed Check boxes from the forms toolbar in cells A1:A300

In format control I want the cell links to be say B1 for A1, B2 for A2 etc.

Is there an easy way to copy the formats down?

Thanks very much

--

Dave Peterson

Thanks very much both of you for coming back.


"Dave Peterson" wrote in message
...
Are you married to the idea of using checkboxes? I find this alternative
easier
to implement and work with:

(saved from a previous post)

Select the range that would have held the checkboxes.
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings (make it as large as you want)

Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a
check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:
=if(a1="","no checkmark","Yes checkmark")

Or you can filter by blanks and non-blanks.

=================
But if you really want checkboxes....

You can use a macro to do the work.

This version uses the cell containing the checkbox as the linked cell. It
also
hides that value in the worksheet (but not the formulabar) by using a
custom
number format of: ;;;

Option Explicit
Sub LayOutCheckboxes()

Dim myCBX As CheckBox
Dim myCell As Range
Dim wks As Worksheet
Dim iCtr As Long

Set wks = ActiveSheet

Application.ScreenUpdating = False

iCtr = 0
With wks
.CheckBoxes.Delete 'nice for testing
For Each myCell In .Range("a1:a300").Cells
With myCell
.NumberFormat = ";;;" 'hide the true/false
iCtr = iCtr + 1
If iCtr Mod 50 = 0 Then
DoEvents
Application.StatusBar _
= "Processing: " & myCell.Address(0, 0)
End If
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!CbxClick"
End With
End With
Next myCell
End With

With Application
.StatusBar = False
.ScreenUpdating = False
End With
End Sub

If you really want to use column B (one cell to the right), change this
line:
.LinkedCell = myCell.Address(external:=True)
to
.LinkedCell = myCell.offset(0,1).Address(external:=True)
and delete this line:
.NumberFormat = ";;;" 'hide the true/false



If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Steve Jones wrote:

I have placed Check boxes from the forms toolbar in cells A1:A300

In format control I want the cell links to be say B1 for A1, B2 for A2
etc.

Is there an easy way to copy the formats down?

Thanks very much

--

Dave Peterson