Resetting number of items in a combobox

[CJR035]

Hello,

I'm relatively new to vba programming but appreciate the helpful tips I've been able to read through other threads, so thank you for that! Here is my issue/goal...

I'm creating a userform to show several pieces of info about a specific client through looking them up on the userform. Info like address, phone number, email, etc.

The userform I've created begins with a textbox. In that textbox, you can type anything in. On Sheet4 of my workbook I have a dynamic list that runs the names of all my clients that contains that text, so that when I use a command button next to my textbox1 to "search" my clients based on textbox 1, it pulls up that dynamic list in a combobox. Then I choose one in the combobox and it shows all of the corresponding information (so that's taken care of) in labels.

I'm trying to figure out how to set up the combobox so that after I run the search with the command button, the number of rows in the combobox will reset based on the number of names that are now on the dynamic list.

I.e. I first search "xxxxxx" and one blank line shows up in combobox1 because no names contain "xxxxxx." Then when I perform a new search with, for example, "and", I should have several names pull up (andy, anderson, etc) but only the one line on combobox1 is visible and only pulls the first name available on the dynamic list. Also becomes a problem if I reverse it and pull up, for example, 20 names on the first search, and my second search yields a few clients with 20 lines visible - so several blank rows after the first few.

How do I adjust the combobox so that it resets the number of rows available after the search?

Please let me know what other info I can give you. There's probably a more efficient way to do this anyway, but almost everything has worked so far.

Cheers

[Norie]

How are you searching and how are you populating the combobox based on the results of the search?

[CJR035]

How are you searching and how are you populating the combobox based on the results of the search?

Command button enters textbox value into cell f1 of sheet4. Then dynamic list is created off of that cell with list of clients that contain that text. Name range of that dynamic list is "names_of_clients". Then combobox rowsource is "names_of_clients".

[Norie]

So what exactly is the problem and what do you mean by 'resetting' the no of items in the combobox?

Are you not getting the expected results listed in the combobox?

Have you checked the named range?

[CJR035]

So what exactly is the problem and what do you mean by 'resetting' the no of items in the combobox?

Are you not getting the expected results listed in the combobox?

Have you checked the named range?

Im getting the expected results in my dynamic list after i enter my search in the textbox.
The problem is that the number of rows showing in the combobox where the results appear does not correspond to the number of names that are found on the search. 1st search... i enter "and". 8 names appear in the combobox. Then i go back to the textbox and enter "a". Over 150 names should appear, but because the first combobox had a list of 8 names, only 8 pines are available when i click the command button the second time. First 8 names out of what should be 150+.

[CJR035]

Im getting the expected results in my dynamic list after i enter my search in the textbox.
The problem is that the number of rows showing in the combobox where the results appear does not correspond to the number of names that are found on the search. 1st search... i enter "and". 8 names appear in the combobox. Then i go back to the textbox and enter "a". Over 150 names should appear, but because the first combobox had a list of 8 names, only 8 pines are available when i click the command button the second time. First 8 names out of what should be 150+.

So it doesnt seem to be a name range issue or anything like that. It seems like it's formatting or creating proper steps in vba for the combobox to adjust the number of rows every time i enter new text in the textbox and hit search.

[Norie]

You don't adjust the no of lines a combobox contains, it shows however many items there are in the range (or whatever) you are populating it from.

The only thing you can adjust the no of lines for is the no of lines that show in the dropdown, and when you set that to a value less than the no of rows in the list you will get a scrollbar.

For example if there were 20 items in the list and you set the no of items to display in the dropdown to be 10 then when you click the combobox's arrow you would see the first 10 items in the list and would be able to scroll down to see the rest.

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[CJR035]

You don't adjust the no of lines a combobox contains, it shows however many items there are in the range (or whatever) you are populating it from.

The only thing you can adjust the no of lines for is the no of lines that show in the dropdown, and when you set that to a value less than the no of rows in the list you will get a scrollbar.

For example if there were 20 items in the list and you set the no of items to display in the dropdown to be 10 then when you click the combobox's arrow you would see the first 10 items in the list and would be able to scroll down to see the rest.

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

I cant upload right now. I guess what im looking to reset is the name range every time there is a new search. The dynamic list changes in number of names based on what i enter in textbox1 after i press the command button. So it always pulls the correct list, but the number of names it shows in the combobox does not change and is always based on my initial search. I dont know if that helps or not. Ill try to get you a dample soon here. Thanks for the input :-)

[Norie]

If your named range is based on a formula then it might need to be recalculated, though I would have thought that would happen automatically.

How exactly are you using the named range to populate the combobox?

Are you just setting it as the RowSource value at runtime?

If you are it might be an idea to set (reset?) it using code after the filter.

[CJR035]

If your named range is based on a formula then it might need to be recalculated, though I would have thought that would happen automatically.

How exactly are you using the named range to populate the combobox?

Are you just setting it as the RowSource value at runtime?

If you are it might be an idea to set (reset?) it using code after the filter.

AHHH you are a life saver!!! I put the rowsource code under the command button instead of in properties for the combobox and it works like a charm. Thank you!!!