Populate Dynamic ComboBoxes

Discussion:

I had this thing working at one point and then I combined several data sheets into one and it wont work now.

PROBLEM:

When I bring the form up, the comboboxes will not populate. Also, when I had the form working originally, the comboboxes would populate with a ton of blanks (from the row count of the date).

Code:

Please Login or Register  to view this content.

ANTICIPATED SOLUTION:

I would love for the comboboxes to dynamically auto-populate again!

NOTES:

This is a portion of a larger post:

http://www.excelforum.com/excel-prog...-updating.html

Rather than looping you could load the lists like this

Please Login or Register  to view this content.

The blank rows are due to you looping for the number of rows in the used range regardless of how many items are actually in the column containing the values.

Oh yeah, get rid of those loops, I love it!

I still can't get it to work though? I added the bit 'Worksheets("COMBOBOX DATA")' before the '.Range' because I thought the code would need to know what sheet to look in. When that didn't work, I deleted it out of there and it still didn't work. I have included the workbook with the code in it, I don't know if I am calling the sub routine correctly or what...

Please Login or Register  to view this content.

you have created your own Initialize routine but not called it.

A userform will always have this signature for it's Initialize event, regardless of what the actual form is called.

Please Login or Register  to view this content.

so you need to either place you code in that event or call your code from that event.

Please Login or Register  to view this content.

You also need to change the other routines to use the sheet refrence/range syntax.
Along with changing the column number used to get last row for a particular column

Please Login or Register  to view this content.

Alright, the new signature for the initialize event seems to be working, thank you - once again, something simple for so many can be such a hassle for a new programer like me.

Next, you changed your code from this:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Why? I see that the 'InitializeSupervisorData' etc. is still the same...

Looking at it some more, I'm thinking that it takes care of the empty list or if there are no items in row 2 for the specific sub routine, is this correct?

Yeah should have mentioned that.

The change was required as a single item will not return an array. So when trying to use it to assign the List property it will fail.

Ideally you would amend similar code to handle such occurances.

Alright, here is my code now:

Please Login or Register  to view this content.

And here is the problem I am still having:

All sorts!

First - when the list is empty, the combobox displays the header as a choice as well as one blank for the TO and FROM comboboxes.

Second - the tubing description combobox displays a header and a blank spot even though there are (and will always be) available choices. Also, did I mention that the header it shows says 'PROPERTY NAME'

Third - the date combobox displays the header DATE and the first date but nothing else

Fourth - the supervisor combobox displays the same as the tubing description (PROPERTY NAME header with a second blank space)

now if I add some choices (3) to my combobox origin ranges (or whatever you call them) for my PROPERTY NAME, PROPERTY NUMBER, and AFE NUMBER...

First - the property comboboxes work fine, they show all 3 choices and they are 'linked' to each other. There is no header listed where there was before though

Second - the tubing description works the same as the PROPERTY NAME dropdown

Third - The date doesn't have the header anymore but only lists three dates instead of all 365

Fourth - the supervisor dropdown is the same as the PROPERTY NAME and TUBING DESCRIPTION

*****

Did I mess something simple?

If you have no items in the sheet then you will need to check that first and do whatever is appropriate for the combobox. Perhaps disable it.

The way it works is it locates the last cell by coming up the column from the last row until it hits a cell with content. For an empty column that would be row 1. The two ranges define then become row 1 and row 2. Where 1 is the Header text and 2 is the empty starting cell.

As I said you need to change the column reference to be the same. So when scanning Column I you need to use the index for column I which is 9 not 3.

Please Login or Register  to view this content.

Same problem applies to the dates, where you should be index column A.

SOLVED!!!

Thank you so much!

Well, I jumped the gun a little on calling it solved. When the list is empty (except for the header), it still shows the header and a blank spot in the drop down menu.

It's not that big of a deal because it's going to get filled and stay filled once the program goes live but it does still kind of make me curious. Any ideas?

Did you add any code to check for empty cell in row 2 of the column. I explained the reason for the occurance with an possible course of action which was to disable the control. I did not provide any code.