centering checkboxes

[Elan Arad]

Hi
I have a worksheet where I add checkboxes on each row so I can decide
wether to include that row in my calculations. I've found some code
that does the job of adding the checkboxes very nicely, for some reason
the checkboxes that are added are all aligned to the top of the sheet
and when getting to lower rows they are not add to the right location.
I was hoping someone could take a look at this and tell me if there's
something I could do to make the checkboxes align themeselves to each
cell they are added to.

code:

Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width,
c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = "123"
.Name = c.Address
End With
c.Select
With Selection

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for
other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change
for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub

 

[Jim Cone]

Your code works for me in XL2002.
I simplified the code a little, but it shouldn't make any
difference as to the checkbox location.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'----------------------------
Sub AddCheckBoxes()
'On Error Resume Next
Dim c As Range
Dim myRange As Range
Set myRange = Selection

myRange.FormatConditions.Delete
myRange.Font.ColorIndex = 2

For Each c In myRange.Cells
c.FormatConditions.Add _
Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE"
c.FormatConditions(1).Font.ColorIndex = 6
c.FormatConditions(1).Interior.ColorIndex = 6
With ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height)
.LinkedCell = c.Address
.Characters.Text = "123"
.Name = c.Address
End With
Next
End Sub
'------------


Hi
I have a worksheet where I add checkboxes on each row so I can decide
wether to include that row in my calculations. I've found some code
that does the job of adding the checkboxes very nicely, for some reason
the checkboxes that are added are all aligned to the top of the sheet
and when getting to lower rows they are not add to the right location.
I was hoping someone could take a look at this and tell me if there's
something I could do to make the checkboxes align themeselves to each
cell they are added to.
code:

Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width,
c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = "123"
.Name = c.Address
End With
c.Select
With Selection

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for
other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change
for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub