+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    Caledonia, WI
    MS-Off Ver
    Excel 2007
    Posts
    2

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

    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

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

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

    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.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    08-02-2012
    Location
    Caledonia, WI
    MS-Off Ver
    Excel 2007
    Posts
    2

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

    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
    Last edited by dosage11; 11-28-2012 at 01:18 AM.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1