Using VB form in MS Excel to retrieve and edit existing information within a workbook

Hi all,

VB novice here so please bear with me.

I have a userform set up with code that successfully transfers data keyed by a user onto an Excel worksheet. It operates through an 'Add Record' button within the worksheet that creates a new record (row) each time. For ease of explanation, let's say the values collected are name, DOB, gender and postcode. The first time the form is used it creates a record of data in row 10 of the worksheet, inserting a unique ref "1" in column A and then the associated data values from the form inserted into subsequent columns. The second time, the data is recorded in row 11 with ref "2" in column A, then row 13 with ref "3" and so on. As I say this works well.

The issue I am now faced with is I need to be able to add functionality so that existing details can be edited. My plan would be that this is achieved through an 'Edit Record' button on the same worksheet that, once selected, would generate an input box where the user can enter the unique ID of the record they need to access details for. Once done, this would then in turn reproduce the original user form with the data held on the worksheet for that record contained. I would then want the user to be able to amend or add to these details using the form and where information has been updated for this to replace the corresponding values that are in the worksheet.

Obviously the routine will need to be able to 'search' for the required unique ref entered in the input box and return the worksheet data for that row within the user form. It will then, after updating, need to be able to replace amended information back into the worksheet at the same location.

As mentioned, the unique ref is automatically inserted into column A of the worksheet by the add record routine. The other data values are as follows:

• First name - column B
• Surname - column C
• DOB - column D
• Gender - column E
• Postcode - column F

I would also need the input box to only allow a unique ID to be entered that exists within the worksheet, eg. if there are records numbers 1-100 I wouldn't want the user to be able to enter 0, or 101. A non-numeric value should also be prevented.

I hope this makes sense and that someone can help with this, I look forward to hearing from you. Shout if anything isn't clear!

(PS I have based the search facility on unique ID recognising that it is entirely possible that there could be different records with the same name, DOB or postcode - if anyone has alternative suggestions however feel free to advise)

try this code as sample

Please Login or Register  to view this content.

Thanks for the quick response, with a bit of fiddling I've managed to get that working a treat

I would really like to be able to restrict the unique ref the user can enter if that's possible with the input controlled so it has to be one that actually exists in column A of the worksheet. For example, if the record ref's range 1to 100 I would like 0 or 101 to not be allowed. I would also like a non-numeric value to be prevented. If you could advise re this too it would be much appreciated !

You need to post this question in to new thread title

Originally Posted by Beam_me_up

I would really like to be able to restrict the unique ref the user can enter if that's possible with the input controlled so it has to be one that actually exists in column A of the worksheet. For example, if the record ref's range 1to 100 I would like 0 or 101 to not be allowed. I would also like a non-numeric value to be prevented. If you could advise re this too it would be much appreciated !

Mark this thread as solved

Thanks for the quick response, with a bit of fiddling I've managed to get that working a treat