Thread: Populate ComboBox with rows that meet criterion

Hi

I think we need to see more of your structure and how you are going to approach things. How about an example workbook.

From your code you somehow identify an ID (this code has been deleted), and you have a combox (cbRows) on a form frmSelect. However, where do you want to load the results of the selection from the combobox? Do you really need to have a form? Maybe you could have the combobox directly on the spreadsheet, and use a dependent list approach with a dynamic named range to fill the combobox?

From what you have described things are doable, but I'm too uncertain of your structure / approach to be able to suggest a solution.

rylo

Thanks, rylo. I am attaching a sample workbook, as you suggested -- and exposing you to my embarrassingly bad programming.

The overall project is to allow clients to use Excel to keep track of their customers [worksheets("customers")] and these customers' authorizations to make appointments with us [worksheets("authorizations")]; and to send us that information every time it changes in a prescribed format (worksheets XDD and XAD).

I don't want them messing directly with the worksheets, so I'll have the "Main" userform, with its command buttons, pop up when they open the file. They would enter data through the various userforms. At the end of the session, upon their clicking the "End" command button, the system would send the XDD and XAD sheets to me by email.

On the "Main" userform, I have (1) Add and Authorize Customer, (2) Modify Customer, and (5) End (save and email) working smoothly. I'm now working on (3) Modify Authorization. For that, I need to choose customer (done) and then (a) find out how many authorizations, if any, the customer has (done) and, if the customer has multiple authorizations (b) find out which one needs to be modified. That's where I'm stuck.

As I mentioned in my original post, I figured I would load a list of the authorizations in a combobox in a userform and let the user choose. But how would I load those particular rows? It seems like it should be easy to do, but I haven't been able to figure it out!

Thanks for listening!
VAL

Val

Have a look at the attached. I've just dummied up something based on your format.

Open the workbook, and then open userform1. This will fill the combobox with the list of customer IDs. Once you make a selection, a second form will open and it will contain a list of the authorizations for that customer id. Make a selection, then press the command button. This will give you a message showing the row of the item that you have selected.

Some things to note.
1) I've used a global variable to transfer the selected id to the second form. More convenience than anything.
2) The combobox on the second form has 2 columns, but only 1 is visible.
3) One column contains the authorization, and the other one contains the row number of the item.
4) The message box shows how you can access the selected item row number.

Once you know the row number of the item that you want to action, then it should be easy to select it, modify it etc.

HTH

rylo

Amazing! Thank you so very much for taking the time to do this. It looks very elegant. I'll let you know how it works out when I plug it into my project.

V.

The code you so kindly provided worked perfectly. Thank you again.

Here's what I ended up doing with it. (I wanted the combobox to have multiple columns, as the index number would not mean much to the user; and I wanted to filter the rows further (to exclude rows marked "D" for delete). I am posting it with my annotations for the benefit of others.

Dim rng As Range
'set lCriterion

With Sheets("Sheet1")
    Set rng = .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
'where C and 3 represent the index column (we'll compare values in this 
' column to lCriterion)
'if you were to choose multiple columns, the first column would serve as
' index (so don't bother)
  End With

For Each ce In rng
'where ce is an undeclared variable
    If ce.Value = lCriterion Then
'where lCriterion is your criterion
      If ce.Offset(0, -2) <> "D" Then
'and here's an additional condition: exclude rows that have "D" in column 1 (i.e.,
' column C minus 2)
      ComboBox1.AddItem ce.Offset(0, -1).Value
'Adds the value in column B (i.e., C minus 1) to the ComboBox1
      ComboBox1.List(ComboBox1.ListCount - 1, 1) = ce.Offset(0, 0)
'Adds the value in column C (ce itself) to the same line of
' ComboBox1 (ListCount -1, x) in the next column (ListCount x , 1)
      ComboBox1.List(ComboBox1.ListCount - 1, 2) = ce.Offset(0, 1)
'Adds the value in column D (ce offset by 1) to the same line of
' ComboBox1 (ListCount -1, x) in the next column (ListCount x , 2)
      ComboBox1.List(ComboBox1.ListCount - 1, 3) = ce.Offset(0, 2)
'And so on so that the user can see the information in 7 columns before
' choosing the row
      ComboBox1.List(ComboBox1.ListCount - 1, 4) = ce.Offset(0, 3)
      ComboBox1.List(ComboBox1.ListCount - 1, 5) = ce.Offset(0, 4)
      ComboBox1.List(ComboBox1.ListCount - 1, 6) = ce.Offset(0, 5)
'      ComboBox1.List(ComboBox1.ListCount - 1, 7) = ce.Row
'Include the line above if you want ComboBox1 to include the row number
' in Sheet1 that the line represents
      End If
    End If
  Next ce