I know that as an object I have to set the width/height etc parameters rather than centre them as such, but try as I might I can't get any of my edits to work in the piece of code I have. Whatever happens these checkboxes insert themselves justified to the left.
I'm only just learning VBA code and am getting to grips with it, but sometimes things still really stump me.
The code I'm using is this:
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 = ""
.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
(code found at interesting blog here: http://blog.livedoor.jp/andrewe/archives/17357484.html)
Can anyone adapt this code to give the checkbox a more central appearance in each cell? Any help would be hugely appreciated
Mads
xxx
Bookmarks