We help IT Professionals succeed at work.

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

58 Views
Last Modified: 2017-04-16
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()
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you attach your workbook please?

Author

Commented:
Please find attached a version with the userform and the code
Example-of-duplicates.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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"?
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.