Somendra Misra
Excel Ninja
Hello VBA Experts,
This must be might first post on a VBA question. So, pardon me if something wrong in title of the thread with actual problem.
So, here it goes, below is a pic of a userform I am creating for data entry by various person across the country. There are 25 combobox's as shown below. As the user select System Id in combobox the other textboxes get autofilled through a VLOOKUP formula. ALL System Id are unique.
The user can select 1 or all 25 System ID, so basically this will generate a dynamic list of 1 - 25 person which will get enter into one of the sheet.
So, I want to put a check that if a user select one system id in any of the comboBox, he should not be allowed to select it second time or in any comboB ox after that.
Basically this is to be done to stop multiple entry for the same name.
Below is the code I am using to extract values in text box as an example.
What I can guess that we need to check the entry for all other comboboxes as the exit or change event of Combobox. I thought of creating a global array available across all the code and if the selected entry is available in that array, it should through a message something like multiple entry and ask to enter again. But I had not worked on global array.
So any help on the issue is much appreciated.
Thanks.
Regards,
This must be might first post on a VBA question. So, pardon me if something wrong in title of the thread with actual problem.
So, here it goes, below is a pic of a userform I am creating for data entry by various person across the country. There are 25 combobox's as shown below. As the user select System Id in combobox the other textboxes get autofilled through a VLOOKUP formula. ALL System Id are unique.
The user can select 1 or all 25 System ID, so basically this will generate a dynamic list of 1 - 25 person which will get enter into one of the sheet.
So, I want to put a check that if a user select one system id in any of the comboBox, he should not be allowed to select it second time or in any comboB ox after that.
Basically this is to be done to stop multiple entry for the same name.
Below is the code I am using to extract values in text box as an example.
Code:
Private Sub ComboBox12_Change()
Set empsheet = Worksheets("Emp Details")
lr = empsheet.Range("c1000000").End(xlUp).Row
On Error Resume Next
Me.TextBox43.Value = Application.WorksheetFunction.VLookup(Me.ComboBox12.Value, empsheet.Range("C2:I" & lr), 2, False)
Me.TextBox42.Value = Application.WorksheetFunction.VLookup(Me.ComboBox12.Value, empsheet.Range("C2:I" & lr), 6, False)
Me.TextBox41.Value = Application.WorksheetFunction.VLookup(Me.ComboBox12.Value, empsheet.Range("C2:I" & lr), 7, False)
End SubWhat I can guess that we need to check the entry for all other comboboxes as the exit or change event of Combobox. I thought of creating a global array available across all the code and if the selected entry is available in that array, it should through a message something like multiple entry and ask to enter again. But I had not worked on global array.
So any help on the issue is much appreciated.
Thanks.
Regards,
Last edited:
