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]

Please clarify the following...

1. How many target ranges do you have?
   
2. Where are your ranges located?
   
3. How many CheckBoxes do you have?
   
4. What are their names?
   
5. Where are they located?

 

[pullmyefinger]

3 target ranges (checkboxes)

I. D9 : D22 ( 14 named as check box 561 - check box 574 )

II. I8 : I22 ( 15 named as 575 - 589 )

III. M8 : M22 ( 15 named as 590 - 604 )
---
44 checkboxes in a worksheet tab called "this month"

Each checkbox is linked to the cell it is above. I know they are not actually "stored" in the cells.


IV. I deleted all the aforementioned checkboxes and redid them as checkboxes 561 to 604 just to rule that possibility out. No luck. 4 or 5 Checkboxes are being stacked one on top of the other to make them "look like" they were deleted. Two boxes were put in 1 cell side by side.

V. Moved the checkboxes and make all their boundaries as small as possible, along with deleting the text names in every single one.

Thanks for helping, Domenic. This is getting to be a pain, but we have gone to far to quit now (if I am "reading" you correctly).

Joe

 

[pullmyefinger]

do you want the actual worksheet?

 

[Domenic]

If there were a number of CheckBoxes stacked one on top of another it would suggest that a cell can contain more than one CheckBox. Is this the case?

 

[pullmyefinger]

2 in one box? has to be because it happened, but the cboxes were still linked to the correct cells. can i send you the worksheet from here?

 

[pullmyefinger]

this won't let me attach the worksheet. email it?

 

[Domenic]

2 in one box? has to be because it happened...

I don't understand. Can you please elaborate?

 

[pullmyefinger]

in cell I8 were the checkboxes for I8 and I9. they looked like they were centered as one "unit".

i was going to send the scaled down version of the sheet but it won't let me attach anything.

 

[Domenic]

Let me try to re-phrase my question. You said that after you ran my macro, there were a number of CheckBoxes stacked one on top of another. For that to happen, it means that there must have been more than CheckBox within a cell to start with. Was that the case? If I understood you correctly from the start, each cell contains a CheckBox, and you simply want to center each one within the cell they're in, is this correct?

By the way, there's no way to attached a sample workbook on this Board. You can, however, provide a link to an image you place elsewhere, such as ImageShack, etc.

 

[pullmyefinger]

ok, one checkbox per cell at the start. they just need to be centered in the cells. there were not 2 checkboxes in any cell to start with.