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]

Alright, in that case, I'll send you my email address via Private Message.

 

[Domenic]

The reason you're having problems is that some of the CheckBoxes extend beyond their cell and into the cell above it. In any case, after taking a look at your file, I've taken a different approach. Try the following instead...

Option Explicit

Sub CenterBoxes()

    Dim wks As Worksheet
    Dim rRng As Range
    Dim rCell As Range
    Dim i As Integer
    
    Set wks = Worksheets("this month")
        
    Set rRng = wks.Range("D9:D22,I8:I22,M8:M22")
    
    i = 561
    For Each rCell In rRng
        With wks.CheckBoxes("Check Box " & i)
            .Width = 27   ' ensures that every CheckBox is the same width - change the width if necessary
            .Height = 17  ' ensures that every CheckBox is the same height - change the height if necessary
            .Left = rCell.Left + (rCell.Width - .Width) / 2
            .Top = rCell.Top + (rCell.Height - .Height) / 2
        End With
        i = i + 1
    Next rCell
    
End Sub

 

[pullmyefinger]

i didn't get any email so i thought you just dropped it.

thanks for the help, your solution looks like one of my old fortran programs.

i kinda thought there was a "border problem" (now i feel just like Texas and Arizona).

lolol, it took a Canadian to fix my border problem, so why don't y'all come down here and fix the rest of the states.. Implement your Vote of Confidence too for all office holders.

 

[pullmyefinger]

I spoke too soon. The checkboxes are lined up under each other, but they are not vertically or horizontally centered in the cells.

Thanks for trying, I will learn this and figure it out so I will know how to do it.

 

[Domenic]

Try replacing...

.Width = 27
.Height = 17

with

.Width = 17
.Height = 18

Also, make sure that the row height for Rows 8 through 22 are the same.

Does this help?

 

[pullmyefinger]

ok, I will do that, But is there a way to take any row and/or column and set its height and width to a variable??

then, no matter what you do the boxes will be centered horiz and vert and the calcs should not be that hard

lol, did you already do that and I don't know it?

 

[Domenic]

Have you tried it?

 

[pullmyefinger]

yes, I did try it, and it aligns the checkboxes one under the other just fine, but as for horiz and vert centering that still does not work.

 

[Domenic]

When I run the code, it seems to align the CheckBoxes both vertically and horizontally. If you send me your email address via Private Message I'll send you a sample file.

 

[pullmyefinger]

Thank you much for all the help, DT, it finally works.