Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Format A Checkbox To The Center Of The Cell

Posted by: LeeAnn Dec 7 2017, 12:56 PM

I have some checkboxes on a spreadsheet I would like to have centered in the cell. I would also like to keep them centered if the row grows. Is there a way to do this? I have looked and it is beyond me on how to do it. Any help would be greatly appreciated. I am not a big fan of Excel as it seems to do something simple turns out to be hard.

Posted by: doctor9 Dec 7 2017, 01:59 PM


Whenever I've worked with checkboxes, it's been in a Template environment, where the users wouldn't be able to change the width of the column where the checkbox control appears. If this is close to what you're doing, try this:

1. Select all of the checkboxes in the same column
2. Select the Format tab on the Ribbon
3. Select Align Left (assuming the checkbox labels are on the right, or missing)
4. Drag all of the checkboxes to the center of the column. Use the left/right arrow keys for small final adjustments.

If you want to actually MOVE the checkboxes after the user changes the column width, you'll probably want to use VBA along these lines:

Private Sub Worksheet_Activate()

    Me.CheckBox1.Left = Columns(5).Left + Columns(5).Width / 2
'   Copy this for each checkbox.  

End Sub

The column number is the column you want to center your checkbox in, of course. You may want to use a different event, or have a command button for the user to click.

Hope this helps,


Posted by: LeeAnn Dec 7 2017, 02:07 PM

Thanks Doc, I am so done with Excel. I can get so aggravated at times with it because I don't quite understand the logic. Finding out the controls are floating and not actually inside the cell was another thing I learned. Never have cared for this application and never will. I will just align like you said and if the customer adjust the size I suppose I will just have to deal with it. It looks ugly but got to deal with it.... Thanks again.

Posted by: doctor9 Dec 7 2017, 02:42 PM


> Finding out the controls are floating and not actually inside the cell

Well... If your checkbox is in column E, and you adjust the width of column C, the checkbox SHOULD move over with column E, unless you have selected "Don't move or size with cells" in the Format Control dialog box. So you got that, at least.

But moving the checkbox while adjusting the column width of the same column where the checkbox is anchored? That may not be what everyone wants. The "floating" aspect is handy because you can place a checkbox almost anywhere - on top of a chart, right on the spot where four cells meet, etc..

If you're willing to let the users alter the sheet design (i.e. the width of a column), I think you just have to accept that you're surrendering a lot of control over the worksheet to them at that point.



Posted by: LeeAnn Dec 7 2017, 03:32 PM

Thanks. I have it to where I am satisfied (not happy but satisfied) with it. I never have liked using Excel but I need it for what I am doing here. I think the users will be OK with it.

Thanks again, you helped me out with it and I learned a little today which is always a good thing.