Userforms - find, retrieve and update information

[JReffold]

Hi,

I'm new to this forum, but have a little knowledge of VBA/Macros. I have created a userform to enter information into an excel worksheet.

I have also created a second smaller userform to enter an ID number (which is generated when the first userform is submitted) which I now want to programme to search and find the ID and then repopulate the first userform with the information already submitted. I need to be able to amend it and then submit the form again - overwriting the existing information for that record.

I've searched numerous sites, and have tried to decipher some existing code, but I'm not winning. Any ideas? I have attached the workbook I am working on.

Many thanks in advance
Jo

[gmr4evr1]

This is from an old post, but might be helpful
http://msdn.microsoft.com/en-us/libr...uildingawizard

[JReffold]

This page doesn't exist any more - but thanks anyway.

[GeneralDisarray]

So, how are they going to identify the record they want to edit?

Will they have the ID in hand, ready to enter it when prompted?


---
Would seem like all you would need to do is recall the entry form, refill the values (like a reverse of what you have in the current code - instead of going from the form objects to the worksheet - turn it around).

BUT! you'll need a way of letting the logic know, when the user presses submit - don't blindly create a new record, but update the existing one.


What you might try is just including a named_formula to hold that value when they press the "edit/amend" button.

Logic would then be:

*Press button.
*Prompt user for the ID with an input box (capture the response and send it to the named formula cell).
*Use a range.find() method in the VBA to locate that record.
*Reverse copy the values in the record to the form.
*Show the form.

Now they do what ever they want. When they press "submit"...

*Your code checks to see if the ID cell is blank (if it is, do what you were already doing)
** If not blank, then this is an edit/amend situation:

*Instead of finding the next blank row (which you were doing for new record) - use a Range.FInd() method to locate the stored value for the ID.
*Update the record - shove data from form to worksheet as per usual.

*FLUSH THE ID - don't forget to blank out that 'id holder' cell! Otherwise you will always think you are editing something.


ALSO! Don't loop to find the next row. Use a single line like this:


i = activesheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

[gmr4evr1]

Odd, the link works from the original post, so here is a link to the original post, then click the link in the last post'
http://www.excelforum.com/excel-prog...nd-update.html

[JReffold]

There is a macro that allows them to check the information they have input - if they identify one that is not quite right, they will have the ID number in the pivot table to reference.

[JReffold]

Odd, the link works from the original post, so here is a link to the original post, then click the link in the last post'
http://www.excelforum.com/excel-prog...nd-update.html

Thanks - the link works now.

[gmr4evr1]

No problem, hope it works for you.