+ Reply to Thread
Results 1 to 9 of 9

Need multiple macro buttons to insert new, formatted rows

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    Oregon
    MS-Off Ver
    MS 365 Enterprise
    Posts
    29

    Need multiple macro buttons to insert new, formatted rows

    I wish this was as simple as it sounds, but I have so many "exceptions" (for lack of a better term) that I have no idea how to set this up. I've attached an example spreadsheet that hopefully helps clarify my explanations.

    I need to have a button for each seciton that will insert a new set of rows, properly formatted so the user can just fill in the blank. For instance, under the "Wages - Form W-2s" section, lets say the user completes the first row (cell D8) and has another to add. We need a button that will add both the row to fill in as well as the spacer row (like row 9). One of the conditions here is that it needs to add the new rows just above the "Check here if additional documents included that are not listed above" row (like row 10).

    I need a separate button for every section on the form that behaves in this manner. Another "exception" is that all of the buttons need to stay in place and not move down the sheet with the addition of rows. They should each be placed either to the far right (preferred) or far left of the page, AND (another exception) should not print when the sheet is printed.

    And if all that wasn't enough, the second page showing to the right of the first page (the Internal Use Only Side) should expand down, page for page, that the ones on the left do, which as you can see from the example is not currently happening.

    Ugh! I feel like I'm asking for a miracle, and maybe I really am, but really hoping a miracle worker sees this and has some help for me!

    Thanks
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-04-2014
    Location
    Oregon
    MS-Off Ver
    MS 365 Enterprise
    Posts
    29

    Re: Need multiple macro buttons to insert new, formatted rows

    As an update, I have created a button with a macro assigned that will add the rows for me, so I have gotten that far. I've attached an updated example. Still need to figure out how to make them not print though and the whole page two thing.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-04-2014
    Location
    Oregon
    MS-Off Ver
    MS 365 Enterprise
    Posts
    29

    Re: Need multiple macro buttons to insert new, formatted rows

    Ok, another update. My macro button doesn't work as well as I thought it did. It seemed to at first on my sample data page. But if I remove the test data in the rows above and use it like it would be used if someone just started filling out the sheet, the lines get added in the wrong place. Back to the beginning I guess!

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Need multiple macro buttons to insert new, formatted rows

    What I've done is
    1) Add a floating command bar to hold your buttons.
    2) Add a InsertTemplates sheet to hold the templates that you want to insert.

    On the InsertTeplates sheet, you can put the templates that you want to insert, formatting etc....
    Each template is separated by '--- in column A (the ' is invisible)
    Note that desired blank rows should be included in your templates

    If you run SetUpCommandBar, buttons will be added to the custom command bar, each button for a different kind of insert. You will be prompted to give each kind of insert a name. (Duplicate names are allowed). The line at the end can be commented out, to hide the InsertTemplates sheet after you've set up the command bar.

    Currently each insert template uses entire rows. If you want to restrict that to fewer columns, change the definitions of the InsertRange variable. or the .Parameter property of the added CommandBarControl.

    The ThisWorkbook code module has been modified to show the custom command bar only on the indicated sheets. And to hide it when appropriate.

    Select a cell, press a button and the chosen insert will be inserted at that point.
    Attached Files Attached Files
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Registered User
    Join Date
    04-04-2014
    Location
    Oregon
    MS-Off Ver
    MS 365 Enterprise
    Posts
    29

    Re: Need multiple macro buttons to insert new, formatted rows

    mikerickson - Thank you for your assistance. I'm sure what you've sent will work like a charm, as soon as I have our local expert help me decipher what I'm supposed to do. You gave instructions on things I've never used and don't know how to do! (Not your fault) Hopefully we can manage the rest on our own, though I'll certainly send a follow-up reply if I need further assistance.

    Thanks so much for your help!!

  6. #6
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Need multiple macro buttons to insert new, formatted rows

    The first step is to set up your insert templates on the added sheet.
    Also, if you don't want the entire row inserted (looking at your file again, I suspect that you don't), how many columns do you want each insert to insert. Is it constant with each insert? Get back to me about that and I can modify things to match.

  7. #7
    Registered User
    Join Date
    04-04-2014
    Location
    Oregon
    MS-Off Ver
    MS 365 Enterprise
    Posts
    29

    Re: Need multiple macro buttons to insert new, formatted rows

    The problem I'm having, is that I just don't understand your instructions. I have never worked with this "insert template" thing you have set up and I have no idea how to go about it. I am guessing that because that is not filled out, this is the reason that all the file does that you sent back is constantly pop-up a window about a run-time error and saying "invalid procedure call or argument". Even if I did know how to handle the whole template thing, I certainly have no idea how to "run SetUpCommandBar". Sorry! I'm sure what you've done is wonderful, but I just don't have the advanced Excel knowledge to understand what I'm supposed to do with this.

  8. #8
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Need multiple macro buttons to insert new, formatted rows

    It sounds like you have almost zero experience with macros.
    If running a macro is difficult for you, my advise would be to continue using the techniques that you have been using.
    In a working environment, its important to not "over run your headlights."

  9. #9
    Registered User
    Join Date
    04-04-2014
    Location
    Oregon
    MS-Off Ver
    MS 365 Enterprise
    Posts
    29

    Re: Need multiple macro buttons to insert new, formatted rows

    Totally agree with you. I will just make due with the basic buttons with the recorded macros assigned to them. To make them not print, I'm just keeping the to the far left and will exclude them from the print area. Thanks again for your help!

+ 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] Macro to Insert Rows with Buttons
    By Cardan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-21-2013, 05:06 PM
  2. 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
  3. Macro Buttons to Insert Rows
    By gusibay2000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2012, 10:34 PM
  4. Insert rows via values of formatted cell
    By christya24 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2009, 10:52 AM
  5. Macro to insert formatted rows
    By oneblueaugust in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-24-2009, 05:05 PM

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