How about an (easier) alternative.
Instead of using a checkbox and having to struggle with size, placement, linked
cell, you could just use a cell.
Select the range
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.
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")
====
If you really want to use a checkbox, you could use code like this to add a
checkbox from the Forms toolbar to a specific range:
Option Explicit
Sub testme()
Dim myCBX As CheckBox
Dim myCell As Range
With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B10").Cells
With myCell
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)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
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
hmm wrote:
I want to use a check box, for example, on each row of a checkbook
spreadsheet to check off if an item has been posted by the bank. To do this,
I have tried placing a check box in a cell in one row of data--using a
fixed-column, relative row reference ($A1) as the cell link--then copying the
row to many empty rows.
The above procedure copies the check box to every row, but unfortunately, it
interprets the cell reference as abosolute--so that every check box refers to
the same entry, not to its own row. It does not matter if I use the check
box from Forms or from Control Toolbox--same problem either way.
Does anyone know how I could create check boxes in multiple rows, each box
linked to its own row?
--
Dave Peterson