+ Reply to Thread
Results 1 to 7 of 7

I need a button to "Add Another Item" into the row(s) above the button.

  1. #1
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Question I need a button to "Add Another Item" into the row(s) above the button.

    I need to get a button to, when clicked, inserts a (copied) named range on another sheet, into the rows above the location of the button as it moves down the page when new "items" are added.

    I'd also like it to be able to number the "items" accordingly, as in the sheet will start with 1 "item" that is numbered "1". I'd like to add the aforementioned range, below "1" and have the newly inserted item automatically numbered "2", and so on.

    I played around with macros and was successful in copying and pasting the range into the area above the button, but it only worked once as the code specifies the row to be inserted and if i tried it again it would insert it into the completely wrong area.

    Here is a screenshot to what I have and what it will look like first opening it.

    Here is a screenshot of what it looks like when I press the button with it's assigned macro... - Notice How it places it above #1 and not in the rows above the Button... I don't want that, ha.

    Here is a screenshot of what I'd ultimately like it to look/act like.


    The Add New Cabinet button (Form) has the macro AddNewCab assigned to it. That Macro's code is currently:
    Please Login or Register  to view this content.
    The Named Range I was talking about is on another sheet (same workbook) that is essentially just a duplicate of what you see on rows 30 to 46. I named it NewCab.

    And the "Numbering" that I mentioned would change the merged cell C34 to the number in the cabinet above it plus 1.

    I hope that makes sense. Also, If I could go about this another way, I'm open to suggestions.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: I need a button to "Add Another Item" into the row(s) above the button.

    You could hide a number or some text under your button

    Search the sheet for your number of text

    And use that to define where your new row goes.


    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: I need a button to "Add Another Item" into the row(s) above the button.

    Hey there,

    Thanks for the response.

    I have attached the files, both "as-is" and how it "should be".

    I hadn't thought to do anything with the cell under the button, that could work maybe!?

    I appreciate any and all help.

    Note: there is no longer any code of my original macro in the attachments.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: I need a button to "Add Another Item" into the row(s) above the button.

    I put the text "Add New Cabinet" into C3 and made the font colour white so it does not show.

    Please Login or Register  to view this content.
    This can be simplified to:-

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 04-30-2016 at 01:31 PM.

  5. #5
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: I need a button to "Add Another Item" into the row(s) above the button.

    wow! This is perfect. It's also way above my head and I had no hope of completing it on my own. I really appreciate the help!

    Heh, that said, I have one I'd like to change. When it inserts the NewCabinet Range, there is an excess row above it, in between the previous cabinet and the newly inserted one (as the NewCabinet Range already included a shorter row "separator"). Where would I make this change?

    Thanks again mehmetcik!

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: I need a button to "Add Another Item" into the row(s) above the button.

    Try this:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: I need a button to "Add Another Item" into the row(s) above the button.

    This doesn't seem to work. It'll add a new cabinet the same as previously and then the next one added is missing a few cells and it just gets worse as you add cabinets. I can just get rid of the empty row that is in NewCabinet and go from there.

    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. Adding "Command Button" and "Messages Box" in VBA Macro
    By sanjeevkumarmc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2014, 07:21 AM
  2. Replies: 1
    Last Post: 10-23-2013, 05:20 PM
  3. Countdown Timer in Excel with a "pause" and "start" button
    By bbhagwat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2013, 05:49 AM
  4. Create a Command Button to "Save As" and "Close" an Excel Workbook
    By thedunna in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-26-2013, 05:38 PM
  5. Help with command button to Save/Lock/Email when the "completed" button is selected.
    By testing the water in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2013, 04:06 PM
  6. Making the Tools>Options...Calculation "Manua"l Vs "Automatic" a Button.
    By Ecoich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2007, 07:50 PM
  7. How to change a Command Button caption from "Enable" to "Disable"?
    By Infinity in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2007, 12:14 AM
  8. Replies: 0
    Last Post: 08-24-2005, 04: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