Reloading and editing data in userforms

**lcoreilly** · 03-24-2010, 06:38 PM

I have created a database in Excel for a client to do data entry.
Right now I have multiple forms set up that they can scroll through
and enter data about different programs. Then at the end they are
prompted to save the record, which runs code to copy all of the data
on the forms to a hidden worksheet.

I need to be able to build in the functionality to allow the user to
save a record half way through completing, and then be able to re-load
the data at a later time, edit it, and their changes would be saved
over the original record.

Unfortunately, creating this database in Access is not an option.
But, that's basically what I need to do - make the Excel forms behave
like Access in that you can scroll through records, edit them, and
changes are saved.

I have been scouring the Internet for examples or information on how
to do this, but haven't come up with any answers. Does anyone have
any insight? I would really appreciate it.

Thanks.

**romperstomper** · 03-24-2010, 06:46 PM

That's a fairly vague question, but you essentially just need a row number variable and then to use that to load whatever controls you have from the relevant columns of the worksheet. If you want to keep the partial records separate, you could use an additional holding worksheet - that way all the code for searching, moving between records and loading is the same, you simply point the form at a different worksheet. Alternatively you could have an additional 'complete' column that you flag as necessary. You can then filter the sheet for complete or incomplete and move through the visible rows as before.
You might want to take a look at John Walkenbach's data form add-in (I think the code is still available for a small fee) or I'm sure there are examples out there.
If you have a more specific question, just let us know!

**lcoreilly** · 03-25-2010, 10:28 AM

Thanks for your help.

I have set up the code to "reverse" so now if I click a button, the data reloads from the hidden worksheets into the forms.

I also created a button for the user to "save completed entry" or "save partially completed entry." If they save a partially completed entry, the data on the form gets transferred over to hidden worksheets "temp_data" "temp_data2" "temp_data3" and "temp_data4"

My question is (and it may be a simple answer, my brain just doesn't seem to be working right now) if the user loads partially completed data, and then chooses to save it (still incomplete) how do I have it overwrite the current entry so that there are not multiple entries each time they go to edit. Also, when they finally say "save completed entry" how do I write the code for it to check if the entry exists in the temp data file, and if so delete it. I can write the code to delete it, I'm just not sure how to identify if it already exists.

Thanks in advance.

**romperstomper** · 03-25-2010, 10:43 AM

If you loaded the data fro the sheet, you should have a row number that you loaded from, so for saving a still incomplete record, you just write back to that row. For a completed record, you can either use a Find method to search for the key value on the completed sheet, or use Application.Match to do the same thing.