Using a macro to insert checkboxes but they are not centering on the height of the cells

I hope I'm not repeating a question but I'm not sure how to search for what I want to do.

When using:

Sub InsertCheckBoxes()
'Update 20140506
Dim Rng As Range
Dim WorkRng As Range
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "CheckBoxInsert"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Ws = Application.ActiveSheet
Application.ScreenUpdating = False
For Each Rng In WorkRng
With Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
.Characters.Text = Rng.Value
End With
Next
WorkRng.ClearContents
WorkRng.Select
Application.ScreenUpdating = True
End Sub

(From someone else)

to quickly insert checkboxes in cells, it works great. However, some of the boxes appear to be resting on the bottom of the rows instead of being centered. Is there a command which will place the boxes so that they are centered vertically in the rows?

A screen shot clip:

boxes.png

Thanks for your suggestions in advance.

Hi HiFive,

The following line controls the position:

Please Login or Register  to view this content.

It is not an elegant solution, but adjusting the left and top may solve your problem:

Please Login or Register  to view this content.

It may take a few trial and error attempts, but by adusting the values in red above, you will probably get the desired results.

I use the following code to delete 'Forms' CheckBoxes:

Please Login or Register  to view this content.

In a similar application I used the following code to create Forms CheckBoxes:

Please Login or Register  to view this content.

I'm pretty sure the code above is from the file associated with post #8 in the following thread: http://www.excelforum.com/excel-prog...as-needed.html

Lewis

Originally Posted by protonLeah

1. Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

2. To attach a Workbook (needed for testing/experiments)
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Thank you Leah.

The code is not what I thought needed testing as it was working well.

A work sheet would not necessarily show the formatting problems. Therefore, I posted the clip.

I will be sure that I use more words to describe the purpose of the post in the future.