+ Reply to Thread
Results 1 to 7 of 7

Macro to Insert Rows with Buttons

  1. #1
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Macro to Insert Rows with Buttons

    Hello,
    I am trying to create a macro that will insert rows when a button (assigned macro to a shape) is pressed. But there are catches; I need to insert a row directly above the button, regardless of which cell is selected in the worksheet. I also plan on putting numerous buttons within one worksheet as I need to add rows to “subtotal” categories.
    The button is the height of the cell. When I click on the button it will insert a row directly above the button (and the button will move down with the cell).
    Any suggestions?

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to Insert Rows with Buttons

    Assuming the button you've created is from the FORMS collection, this one line of code can be used to do what you want. It spots the TopLeftCell of the where the button currently resides on the sheet, and pushes that row downward, effectively inserting a blank row where the button was.

    Please Login or Register  to view this content.
    I would create one button, add that code to the button, then copy that button to other locations so they're all linked to the same Click event in a ready manner.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Macro to Insert Rows with Buttons

    It worked! This is much more elegant and simple than I had imagined. Thanks!

  4. #4
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Macro to Insert Rows with Buttons

    Hi Jerry, I copied the macro per your advice and it works great. After using it, I realized I need to add formatting to the row that was just inserted. I need it to match the formatting of the row directly above, including cell borders. Is this something that can be added easily? Thank you again...

  5. #5
    Registered User
    Join Date
    11-21-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Macro to Insert Rows with Buttons

    Try adding the following line:

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 11-21-2013 at 04:53 PM. Reason: Added CODE tags, please read the Forum Rules (link in menu bar above). Thanks.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to Insert Rows with Buttons

    To take complete control of it:

    Please Login or Register  to view this content.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  7. #7
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Macro to Insert Rows with Buttons

    Works great! Thank you again..

+ 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. How to insert and user of Buttons, Radio buttons, check boxes in excel sheet.
    By krishco in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2013, 02:52 AM
  2. How to insert Buttons, radio buttons and check boxes in Excel
    By krishco in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2013, 08:13 AM
  3. How can I insert multiple sets of option buttons (radio buttons) in Excel 2010?
    By mickwooduclan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2013, 10:01 AM
  4. Macro Buttons to Insert Rows
    By gusibay2000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2012, 10:34 PM
  5. Insert command buttons during macro run
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-21-2011, 07:26 PM

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