Populate a combobox when opening a workbook

[Guido Meng]

Hi!

I need to populate a combobox upon opening a workbook. I've tried the following but I keep getting a error message stating that the "ComboBoxNOMBRES" variable has not been found. Where am I going wrong here?

Please Login or Register  to view this content.

Will appreciate any insight on this!

Thank You!

[abousetta]

Hi,

Can you explain in words what you are trying to do?

Initial thoughts...

.Range("A:A").Rows.Count = 1048576 but did you mean the last row that is used in column A [e.g. .Range("A" & Rows.Count).end(xlUp).Row)]

abousetta

[Guido Meng]

Hi abousetta

Thanks for your reply!

Well I'm an amateur when it comes to programming but I really like it. I'm telling you this because sometimes I think I go the long and not very efficient way of doing things programmatically speaking. With those two lines of code that determine how many cells have any content and thus, how many items will be added to the combobox list what I do is this:


1) MideLista = .Range(.Cells(20, 2), .Cells(.Range("A:A").Rows.Count, 20)).Rows.Count < -------------- COUNTS HOW MANY ROWS ARE AVAILABLE IN THE "INGRESO LECTURAS" SHEET. DEPENDING ON WHAT EXCEL VERSION IS BEING USED EG. 65,532 ROWS FOR EXCEL 2007 OR OLDER OR 1,000,000 FOR NEWER VERSIONS.

2) Once the MideLista Variable has this number the following line of code determines how many cells are NOT Empty

MideLista = MideLista - Application.WorksheetFunction.CountBlank(.Range(.Cells(20, 2), .Cells(20 + MideLista - 1, 2)))

I guess there are more advanced and elegant ways of doing this but that part of the code has worked for me in this little application as well as in other applications .


Now, what I'm trying to accomplish here, is to populate the ComboBoxNOMBRES control with that range of values (from the cell (20,2) to the cell (20 + Midelista+1) the ComboBox Control is an ActiveX control which has been placed in the "Ingreso Lecturas" sheet. I've already written a routine that populates this control with items according to partially entered text, for example, if the letters "Jo" are typed in the control, the routine extracts only the items that contain or begin with those two letters (depending on the desired modality). If there's no text in the control (for example if the user deletes a full or partially entered entry with the backspace key) then combobox control is populated with the whole, unfiltered list. What I'd like to do is to be able to populate the control with the whole unfiltered list the moment the workbook is opened. I get the desired result but only after typying and deleting the text on the control. Not sure if I've conveyed an accurate description of my problem, sorry for that as English is not my first language and I don't get to practice it enough .

[abousetta]

Hi,

Have a look at the example in the attachment. The code I use to fill the combo box is as follows:

Please Login or Register  to view this content.

It will work fine as long as there are no empty spaces. If there empty spaces in Column B between row 20 and the last used row then you will need some additional coding or else you will get intermittent blanks in your list. Additionally, if there are duplicates then we should manage that also so that all the items in the list are unique.

Have a look and let me know your thoughts.

abousetta

[Guido Meng]

Hi Abousetta!

Your code works very well when the combobox to be populated is inside a form. I liked your approach -to determine the range that contains the list- better than mine, thank you for that!. I have the combobox placed directly in the sheet and I tried invoking the "Private Sub Workbook_Open()" event from then "ThisWorkBook" object but i didn't work. I finally opted to use the following solution but I suspect there might be better ways to get the desired result:

Please Login or Register  to view this content.

It worked but I'm still confused as to why the WorkBook_Open() event wasn't fired (although I coun't verify this with any degree of certainty as once a workbook is closed, the breakpoints placed in the code seem to be eliminated). I would like to know your thoughts as to why the event doesn't fires.

Thank you!!

[abousetta]

Hi,

Here is an example (modified from the one I posted above) that uses a combobox within a sheet.

Please Login or Register  to view this content.

Once you open the workbook, it populates the first shape on the first sheet (which in this case is a combobox on sheet 1).

Let me know if this works for you.

abousetta

[Guido Meng]

Hi abousetta!

The code worked very well with only two tiny modifications, them being as follows:

Please Login or Register  to view this content.

Summarizing, I finally got what I wanted with your help, Thank you very Much!!!

[abousetta]

Thanks for the feedback and glad to see you got it working. If you are satisfied with the results then please mark the thread as solved.

Good luck.

abousetta