VBA: Search/replace (if ID exists) or append new data row (for new ID)

[CatyH]

I need to send the information from tab 1 (Enter Student Data) into the datatable of all students (tab 2: StudentInfo). Detect when record exists and replace (or prompt user to verify and then replace)... or append when new record

3 tabs:
1. Interactive "Enter Student Data" tab. A user-friendly form where 25 pieces of information are entered. At the top is an open data-validation (that is, user not limited to selected values only) box. If the ID exists, the form gets populated with the information found in the "StudentInfo" tab*. If the ID does not exist, the user sees a blank form ready to receive data
2. StudentInfo tab. this is a standard dataset. Column A is the Student ID (upon which the search is made in the "Enter Student Data" tab. rest of rows are info from "Enter Student Data" tab.
3. "StudentDataCalc" tab. Basically just one intermediate row of data pointing to the info in the "Enter Student Data" tab. This would be the one row of data that would either REPLACE an existing row (based on student ID) OR would be APPENDED if the StudentID did not already exist (in the StudentInfo tab)

*(I might also mention that i'm thinking I'll be setting up a trigger to detect cell change when value is entered in Student ID (in "Enter Student Data" tab). and if the ID is found (condition TRUE), the cells in "Enter Student Data" tab will be populated with the lookup values. If the ID is not found (condition FALSE) the cells are cleared to make way for new data.

So... how do I create the sub (worksheet level, right?) that will detect an existing ID, then replace the row... OR append to the next blank row if the ID does not exist...?

Thank you in advance for any guidance you may provide!!!

 

[COwen]

Okay, what you want is possible. However, would you consider letting me build you a UserForm instead? It will be easier and dare I say, more user friendly.

Here is a sample. A UserForm IS Excel. They are very simple to use and make a great UI.

 

[COwen]

In my sample, when you mouse over the button color changes. Not difficult to do, but adds a professional look.

 

[CatyH]

Okay, what you want is possible. However, would you consider letting me build you a UserForm instead? It will be easier and dare I say, more user friendly.

Here is a sample. A UserForm IS Excel. They are very simple to use and make a great UI.

View attachment 11429
View attachment 11430

Thank you for the suggestion of using a UserForm. I considered that but wanted to minimize the whole "popup" experience as the set of users I'm interacting with are VERY VERY VERY un-excel / un-tech savvy. But if the interface I'm envisioning is not possible, I'll investigate the UserForm. I have used those for small tasks, but not for search/replace|Append variations. will check it out!
Thanks!

 

[COwen]

Certainly possible. Userforms can be made to look more natural than just a popup. If you send me a link to a cloud location with your book, I will be happy to take a look at it for you.

 

[quanglyvn]

If you like simplicity, you can do the following

File on MEGA

mega.nz