+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    06-11-2015
    Location
    Baltimore, Maryland
    MS-Off Ver
    2007
    Posts
    3

    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.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Using a macro to insert checkboxes but they are not centering on the height of the cel

    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)
    Ben Van Johnson

  3. #3
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Using a macro to insert checkboxes but they are not centering on the height of the cel

    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

  4. #4
    Registered User
    Join Date
    06-11-2015
    Location
    Baltimore, Maryland
    MS-Off Ver
    2007
    Posts
    3
    Thank You Lewis for your post. I will give it a try as soon as I get back to a real computer.
    Last edited by HiFive; 06-12-2015 at 05:47 AM.

  5. #5
    Registered User
    Join Date
    06-11-2015
    Location
    Baltimore, Maryland
    MS-Off Ver
    2007
    Posts
    3
    Quote Originally Posted by protonLeah View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VB Macro to Auto Insert Two Checkboxes For Each Image in Excel 2007 Workbook
    By jcook1100 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 10-21-2014, 10:22 AM
  2. [SOLVED] Centering checkboxes in VBA code
    By Madraykin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-12-2012, 11:10 AM
  3. insert elemnts from cells into array, create a userform with values checkboxes
    By usadream in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-31-2006, 11:30 AM
  4. centering checkboxes
    By Elan Arad in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-11-2006, 01:55 PM
  5. Centering a Userform after Height Change
    By Craig in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-28-2005, 05:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1