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
Bookmarks