Link to home
Start Free TrialLog in
Avatar of Nico Caprez
Nico Caprez

asked on

Check for duplicates from current selection in combobox and compare to existing values in column

Hi,

I have a userform with 6 comboboxes and 2 option buttons - all together form a unique identifier. However, there is an ultimate option button which says if the value from all the fields in the userform  is "new" or "updated". I would like to check, when clicking on the "update" option button, if the "unique identifier" exists already in a specific column in the worksheet. If the value does not exist in the sheet, a msg box should pop up and don't allow the user to choose update since there needs to be a "new" value first. Furthermore, if I click on the submit button at the end and I have selected "New" without touching the Update button, it should switch automatically to "Update".

How can I concatenate all the comboboxes and option buttons to potentially have the same unique identifier in a stored script or whatever to check within a specific column before allowing to press the button to send the data to the next empty row.

I think this should be done in the UpdateOptionButton_Click() sub for the update-scenario and the overwrite-scenario at the OKToggleButton_Click()
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Can you attach your workbook please?
Avatar of Nico Caprez
Nico Caprez

ASKER

Please find attached a version with the userform and the code
Example-of-duplicates.xlsm
Let me first suggest a different approach. Have two buttons on the worksheet, one for "New" and the other for "Update". Then if the "New" button is clicked, your userform shows up and when the user clicks "OK" the code behind that button checks to see if a row with that data exists, and if it does, an error message is displayed. If the user wants to do an update, he first selects any cell in the row to be updated and then clicks the "Update" button and the userform opens with the data from the row of the selected cell. As an alternative to the update process, you could have what looks like a button in column "L" of each row of data, and when that "button" is clicked, the userform would open with the data from that row.
I like the idea with the two buttons on the worksheet. My only concern is that the worksheet will have a lot of data in it and therefore many rows which is why I don't want to go and search for the exact row which needs to be updated and secondly I still need the value with the first update to be able to track the updates.
My only concern is that the worksheet will have a lot of data in it and therefore many rows which is why I don't want to go and search for the exact row which needs to be updated
Code could be added to keep the data sorted.
I still need the value with the first update to be able to track the updates.
Please explain more fully what you need to track.
I don't want to overwrite any values, it should be a growing database and every new entry should be a new line-item so that I can track updates for the respective duplicate.
Sorry, I'm still not sure I understand. Can you provide a sample workbook that shows what you want to store from the results of a couple of updates, and explain what you want to be able to do with the "database"?
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial