Update&Modify excel Database

**surya9** · 12-30-2009, 02:43 AM

Hi All
I have an excel sheet where I am trying to make database. In the first(Entry) sheet the person enters the data and submits. The same is added in the second (Database) sheet with the first two columns is added by default in the database sheet. I have the code for the same.

Now, I want excel to have a search box in the sheet 1 and when the person enters the Emp ID, excel should validate the username, if the username is matching with the current user, it must display the all the field back in the sheet 1 from sheet 2 for editing and resubmitting. When resubmitted the current record must overwrite previous record.

If the username name does not match then user must not be able to view the data.( this is just to keep other people’s data confidential).

Also in the sheet 2 I need the headers based on the sheet 1 fields (This is not mandatory. I can enter them as a onetime activity)

Request someone to help me…
Attached is the excel sheet temaplate and code

Thank you in anticipation

**royUK** · 12-30-2009, 05:12 AM

You don't need VBA, just use a similar layout to the existing form then use VLOOKUP based on the ID. Add a Data validation list of IDs to choose from

**surya9** · 12-30-2009, 07:55 AM

Hi Roy

Thank you for the suggestion...

I understand the solution will work, but it is huge round-about process. First I need to edit my current macro to place the username to the last and create a new tab with similar format and have the validation and Vlookup done to each of my cells in the new tab. But if I have to edit the same, I again need to catch my macro, modify the code in a way that the user value in the vlookup cell has to be chaged to value when I submit to database. This means a vlookup is an obstacle in this case.
This will also not serve the purpose as this will add a new record for the same user. I need to overwrite the previous record when modified.

Please suggest a macro code which will enable the user to view the already entered data in the same cells sheet 1 or the place where he entered for the first time.

An exemplary for this would be a webpage where I search the data edit the feilds and resubmit it.

I hope I am clear. please let me know incase of any clarifications.

Thank you