+ Reply to Thread
Results 1 to 4 of 4

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

Hybrid View

  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:

    Private Sub UserForm_Initialize()
      
      'In addition to this code a Name was defined in the Formulas section
      ' of the sheet. "=OFFSET(ESAT Functions!$B$1, 0, 0, COUNTA(ESAT Functions!$B:$B),1)"
      'Populate Functions combo box from the list of functions in spreadsheet
      
      Dim rngFunction As Range
      Dim ws As Worksheet
      Set ws = Worksheets("ESAT Functions")
      
      For Each rngFunction In ws.Range("Function")
            Me.cboFunctions.AddItem rngFunction.Value
      Next rngFunction
        
      Set ws = Nothing
    End Sub
    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:

    Private Sub GetRowNumber(row)
        row = cboFunctions.ListIndex + 1
    End Sub
    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:

    Private Sub UserForm_Initialize()
      
      'In addition to this code a Name was defined in the Formulas section
      ' of the sheet. "=OFFSET(ESAT Functions!$B$1, 0, 0, COUNTA(ESAT Functions!$B:$B),1)"
      'Populate Functions combo box from the list of functions in spreadsheet
      
      Dim rngFunction As Range
      Dim ws As Worksheet
      Dim row As Integer
      
      Set ws = Worksheets("ESAT Functions")
        
      'Variables for Message Box
      Dim msg As String
      Dim title As String
      Dim answer As Integer
      
      msg = "Would you like to load just your favorites?"
      title = "Load Favorites"
      
      answer = MsgBox(msg, vbYesNo, title)
      
      Dim rowNumber() As Integer
      Dim i As Integer
      i = 1
      
      'Load the combo box
      For Each rngFunction In ws.Range("Function")
        ReDim Preserve rowNumber(0 To i)
        row = rngFunction.row
        If answer = vbYes Then
            'Load the items marked as Favorites
            If Cells(row, 4).Value = "X" Then
                Me.cboFunctions.AddItem rngFunction.Value
                rowNumber(i) = row
                i = i + 1
            End If
        Else
            'Load all items
            Me.cboFunctions.AddItem rngFunction.Value
            rowNumber(i) = row
            i = i + 1
        End If
      Next rngFunction
       
      Set ws = Nothing
    End Sub
    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,644

    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.
    Me.cboFunctions.AddItem rngFunction.Value
    
    Me.cboFunctions.List(Me.cboFunctions.ListCount-1,1) = rngFunction.Row
    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,644
    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