I am new to this forum and hope someone can help me with an Excel VBA-question.
At this moment I am developing an application in Excel with VBA. I have created a userform with a (multiselectsingle) listbox (with 2 columns). The first column is for a supplier name the second for the name of the factory.
When an user double clicks an empty cell in the supplier column of the worksheet my userform pops up and shows all suppliers in the listbox and with the name of the associated factory in the second column of the listbox.
By clicking OK in the userform the selected supplier is stored in the active cell and the related factory will be placed in the same row but of course in a different column. So far so good.
Some suppliers have more than one factory. Therefore in my listbox some supplier names are shown more than once because they have a different factory in the second column.
If a user double clicks on a non-empty cell of the supplier name in the worksheet I would like to show the userform with the focus (cursor) on that supplier with the combination of the factory that is the same row of the worksheet.
Until now I have not been able to do that. To get the focus in the listbox on the current supplier name is no problem (frmSupplierFactory.lstItems.Value = CStr(ActiveCell.Value), but how can I "tell" Excel that it also has to take into account the selected factory that is in the worksheet? At this moment my code will always select the first row of that supplier without looking at the previously selected combination of factory.
Your help will be highly appreciated.