trying to get cell location of an item selected in a combo box

[dosage11]

Hello,

I've created a user form that contains a combo box that it populated based off of the items contained in a column within a spreadsheet:

Please Login or Register  to view this content.

Using the user form, the users will enter data on to the spreadsheet. The selected item determines what row the data will be placed. I used the following to be able to get the row when needed:

Please Login or Register  to view this content.

This has all worked flawlessly. What I'm now trying to do is allow the users to create a smaller list for the combo box. Currently there are about 150 items that populate the combo box. Not all users need to get to all 150 items regularly, so I'm allowing them to mark their 'Favorites' by placing an 'X' in a designated column. Then when the user form loads they have the option to load just their 'Favorites' or the entire range:

Please Login or Register  to view this content.

When I had tried doing this without the array in the code above the items would populate the combo box properly, but now the ListIndex that I was using for the GetRowNumber procedure was causing the wrong rows to get filled with the data. I created the array so that I'd be able to have each item's row # from the spreadsheet stored with it's order in the combo box. For example, if the first item in the combo box came from row #3 in the spreadsheet in the array it would be stored as rowNumber(1) = 3.

What I'm trying to accomplish is in later procedures I'd like to be able to get the row number of the item that is selected in the combo box. The problem is I don't know how to recall the information from the array I filled in the earlier procedure. Either that, or if there's a different way to get the cell location of an item from the combo box I loaded in that earlier procedure it would be easier to do that, I'm sure. If I'm making this way too complicated I'd appreciate any assistance to simplify.

Thanks in advance,

Jason

[Norie]

Jason

You've got the right idea, you need to store the row somewhere.

I would suggest storing it in the combobox in a hidden 2nd column.

If you had a 2 column combobox you could add the row in the 2nd column like this.

Please Login or Register  to view this content.

[dosage11]

Norie,

Thank you very much. I knew there had to be a better way, and there it is. Using that, hiding the new column that I added to the combo box that is now containing the row #, making the hidden row the bound column, and then recalling that Value when calling the GetRowNumber procedure does the trick. Very awesome! Thank goodness. I think once I started going down the path with the array I just got a lot of tunnelvision. Your suggestion is a lot simpler.

Thanks again,

Jason

[Norie]

Jason

Glad it worked.

You can take it further and actually load all the data into further columns of the combobox.

Not a big fan of that myself but I've seen it a few times.