+ Reply to Thread
Results 1 to 10 of 10

Add data to range using MsgBox

  1. #1
    Registered User
    Join Date
    04-25-2008
    Posts
    6

    Smile Add data to range using MsgBox

    Hi,
    I'm trying to add data to a list in a range using a MsgBox. I need to look for the next empty row in the range and expand the range as necessary while not overwriting stuff below the current range. I have the code below which adds to the 'database', but doesn't look in the Range (Doc_List). SO I guess I need to insert the data rather than append in the range so any data below the range doesn't get overwritten.
    Thanks


    Please Login or Register  to view this content.
    Last edited by rylo; 04-25-2008 at 04:33 PM.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Firstly, can you please read the forum rules and make sure you wrap any code. I've done if you this time.

    Can you please put up a sample file showing your data positioning, the form and the code. I'm not sure from your code exactly where things are based on your comment:

    next empty row in the range and expand the range as necessary while not overwriting stuff below the current range
    rylo

  3. #3
    Registered User
    Join Date
    04-25-2008
    Posts
    6

    Here's a sample

    Here's a simple sheet with my code (and some experimentation). I'm trying to have a msgbox for data entry. I want the data to be inserted in the rows after the 'Number/Title/Rev/Status" Row and to fill in without overwriting cell a9. In other words, the data is inserted in to each row and a9 gets shoved further down the sheet.

    Sorry about the non wrap. Won't happen again.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    try changing the row

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    I think that should do it.

    But what do you want to do with the new data? Do you want to insert a new row to handle, or put somewhere else?

    rylo

  5. #5
    Registered User
    Join Date
    04-25-2008
    Posts
    6
    The only thing I do with this data is track status. It's part of a series of planning sheets to get ready for a test. I thought it would be much easier to input via MsgBox rather than directly into the sheet. I have other checks going on and people have mucked about not understanding what they were doing and messed up other spreadsheets. I'll give this a try. The issue I see, if I understand, is that if there isn't enough rows, you can't input data. I really want each input to be a new row.
    Thanks

    Quote Originally Posted by rylo
    Hi

    try changing the row

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    I think that should do it.

    But what do you want to do with the new data? Do you want to insert a new row to handle, or put somewhere else?

    rylo

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Try this

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Registered User
    Join Date
    04-25-2008
    Posts
    6
    Thanks RoyUK, I tried this but the first entry overwrites cell a9 instead of putting the data in A4. There's something about the up/down commands in a range that eludes me. It seems to behave differently in a range depending on how the range is selected. I see different behavior for an asending range selection as opposed to descending. Very ...unusual.

    I'm wondering if the best way to go is just select cell a3, down to next text, offset 1 row down, then insert row and copy data. It's not as elegant but it will probably work.

    Any thoughts?

    Update: I don't know what I did, but is no longer overwriting A9. However, I'm not getting data in sequential rows of data. It seems to be always writing into cell a4. It's as if the x1down command has no effect.
    Last edited by dmspen; 04-28-2008 at 11:22 AM.

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    You provbably need to reset iRow

    Please Login or Register  to view this content.
    Last edited by royUK; 04-28-2008 at 11:41 AM.

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    I've just tested the code but your table contains Merged Cells which will make all your cell references wrong. Never use Merged Cell.

  10. #10
    Registered User
    Join Date
    04-25-2008
    Posts
    6
    Quote Originally Posted by royUK
    I've just tested the code but your table contains Merged Cells which will make all your cell references wrong. Never use Merged Cell.

    OK, I unmerged all cells. They were really for looks anyway and I'm still getting an overwrite on cell a4 for every entry.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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