Populate ComboBox with rows that meet criterion

[val64]

Hi, Everyone,
I have a worksheet that includes 8 columns. Column 2 is the index. Column 3 has IDs.
Given an ID, I want to know how many rows in the worksheet include the ID in Column 3.
If more than one row, I want to load each such row into a ComboBox so I can then select the row I want to work with.
So far, I’ve been able to count the rows that have the ID in question. But I can’t figure out how to load the rows into a ComboBox. I’d be grateful for any help!

Please Login or Register  to view this content.

[rylo]

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

[val64]

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

[rylo]

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

[val64]

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.

[val64]

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.

Please Login or Register  to view this content.