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]

Do you mean that you'd like the first CheckBox to be centred in the first sub-range (G4:G6), and the second CheckBox in the second sub-range (K10:K11)? And does this mean that you will always have one CheckBox available for each sub-range? If so, maybe...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CenterBox()

    [color=darkblue]Dim[/color] Rng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] ChkBx [color=darkblue]As[/color] CheckBox
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] Rng = Worksheets("P").Range("G4:G6,K10:K11")
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ChkBx [color=darkblue]In[/color] ActiveSheet.CheckBoxes
        Cnt = Cnt + 1
        ChkBx.Left = Rng.Areas(Cnt).Left + (Rng.Areas(Cnt).Width - ChkBx.Width) / 2
        ChkBx.Top = Rng.Areas(Cnt).Top + (Rng.Areas(Cnt).Height - ChkBx.Height) / 2
    [color=darkblue]Next[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[pullmyefinger]

there are two ranges in this example and in my worksheet. they are in different columns and different rows.

i want something that will navigate the first set of checkboxes in the first range and center those checkboxes in their cells, then go to the second set of checkboxes in the second range and center those also.

E. G., go to G4 and center g4, g5 and g6. after that, go to k10 and center K10 and K11, then quit and/or make some other cell current (range...select).

 

[Domenic]

Hopefully I've understood you correctly. The following macro will loop through each CheckBox in your sheet named "P", and will centre each CheckBox that is located within your ranges "G4:G6" and "K10:K11".

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CenterBoxes()

    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] rRng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] rCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] ChkBx [color=darkblue]As[/color] CheckBox
    
    [color=darkblue]Set[/color] wks = Worksheets("P")
    
    [color=darkblue]Set[/color] rRng = wks.Range("G4:G6,K10:K11")
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ChkBx [color=darkblue]In[/color] wks.CheckBoxes
        [color=darkblue]Set[/color] rCell = ChkBx.TopLeftCell
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(rRng, rCell) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            ChkBx.Left = rCell.Left + (rCell.Width - ChkBx.Width) / 2
            ChkBx.Top = rCell.Top + (rCell.Height - ChkBx.Height) / 2
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[pullmyefinger]

it centers g4-g6 but not k10-11. i tried it with one contiguous range in my worksheet and it erased or moved two checkboxes and did not center the rest.

 

[Domenic]

Maybe for some of your CheckBoxes the top left part of it is located to the left of your ranges. Does this help?

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CenterBoxes()

    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] rRng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] rCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] ChkBx [color=darkblue]As[/color] CheckBox
    
    [color=darkblue]Set[/color] wks = Worksheets("P")
    
    [color=darkblue]Set[/color] rRng = wks.Range("G4:G6,K10:K11")
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ChkBx [color=darkblue]In[/color] wks.CheckBoxes
        [color=darkblue]Set[/color] rCell = Intersect(rRng, Range(ChkBx.TopLeftCell, ChkBx.BottomRightCell))
        [color=darkblue]If[/color] [color=darkblue]Not[/color] rCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            [color=darkblue]Set[/color] rCell = rCell.Cells(1, 1)
            ChkBx.Left = rCell.Left + (rCell.Width - ChkBx.Width) / 2
            ChkBx.Top = rCell.Top + (rCell.Height - ChkBx.Height) / 2
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[pullmyefinger]

just got back from fixing 2 laptops.

question: can I set individual ranges for rRng and do the "for each checkbox.." idea multiple times??

like i said, the first range works (g4:g6) but the second range it doesn't touch, So can I do your "for each" loop 3 times in the same Sub??

Maybe that will cure the problem of it not finding the second range it seems.



I will try that tonite and let you know what happens. Thank You for all the help.

 

[pullmyefinger]

Below is what I got to work. Thanks Again to ALL for the help!

I would not have known how to do the actual centering with the (.topleftcell) and all that other stuff.

Centering something is easy, but I did not know the specific properties to use. lol, i didn't even know they existed...

Now that it works I can consolidate statements.

======================

Sub CenterBox()
Dim wks As Worksheet
Dim exprange, taxrange, buyrange As Range ' was rRng
Dim rCell As Range
Dim cbox As CheckBox 'was ChkBx


Set wks = Worksheets("P")

Worksheets("P").Activate

Set exprange = wks.Range("G4:G6")

' was for each ebox in wks.checkboxes BELOW

For Each cbox In wks.CheckBoxes
Set rCell = cbox.TopLeftCell
If Not Intersect(exprange, rCell) Is Nothing Then
cbox.Left = rCell.Left + (rCell.Width - cbox.Width) / 2
cbox.Top = rCell.Top + (rCell.Height - cbox.Height) / 2
End If
Next

Set taxrange = Range("K10:K11")

For Each cbox In wks.CheckBoxes
Set rCell = cbox.TopLeftCell
If Not Intersect(taxrange, rCell) Is Nothing Then
cbox.Left = rCell.Left + (rCell.Width - cbox.Width) / 2
cbox.Top = rCell.Top + (rCell.Height - cbox.Height) / 2
End If
Next
Set buyrange = wks.Range("L6:L10")

For Each cbox In wks.CheckBoxes
Set rCell = cbox.TopLeftCell
If Not Intersect(buyrange, rCell) Is Nothing Then
cbox.Left = rCell.Left + (rCell.Width - cbox.Width) / 2
cbox.Top = rCell.Top + (rCell.Height - cbox.Height) / 2
End If
Next
End Sub

 

[pullmyefinger]

One other note I did not know till now.

When I told you before that your routine blew away/erased/moved some of the checkboxes, I did not know the checkboxes had to be "named sequentially" within a range.

I used my routine that worked and it did the same thing just mentioned.

If you have a range A12:A14, the checkboxes have to be sequentially numbered as in A12 is check box 1, A13 is check box 2 and A14 is check box 3. That is what I had to do to get it to work right.

 

[pullmyefinger]

Crap, still doesn't work. Same cells missing checkboxes, and now there are two checkboxes in one cell. the idea of sequential naming worked in the first example but not the second, actual worksheet. Need to see what is different.