centering checkboxes in non-adjacent ranges

[pullmyefinger]

this code was originally given by QuietRiot or someone, I don't remember.

I can't get my additions to work and don't understand how to setup or navigate all the cells in a range(s).

Can someone explain what is missing?? The example given in the forum does not work so something must be missing or I don't understand how to apply it to my sheet or any range(s).

My example ranges are G4:G6,K10:K11 in my practice sheet.




Sub CenterBox()

Dim rng As Range
Dim Box As Object

Worksheets("P").Activate

Set rng = Worksheets("P").Range("G4:G6,K10:K11")


For Each Box In ActiveSheet.CheckBoxes
Set rng = Box.TopLeftCell
Box.Left = rng.Left + (rng.Width - Box.Width) / 2
Box.Top = rng.Top + (rng.Height - Box.Height) / 2
Next
End Sub

 

[Domenic]

You're very welcome!