Centering checkboxes in VBA code

[Madraykin]

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

[Md Aejaz]

Hi Madraykin,

Dear the check box will be created at active cell means the cell which you have selected or the cell in which cursur lies.
and it will link this check box to that active cell .
the word selection in your code is the range object that passes the address of active cell to the code .

so where ever u want that check box select that cell and run the code .then it will work.

Hope this will solve your proble.


If u r Satisfied with answer ,please select star t the bottom.

Thanks

[Madraykin]

Sorry - I'm not sure I understand?

This code works fine to insert a checkbox in each cell in a range of cells that I select, and links them perfectly to the relevant cells. What it doesn't do is format the checkbox itself to appear 'centred' in the cell, which is what I would like to do.

What I need to know is how to adapt the left/top/width/height to make it appear central.

Sorry if I wasn't clear

Mads

[Madraykin]

No worries, I found a workaround by merging cells

Thanks all!