Add values to a ComboBox in a dynamically created UserForm

[lionne]

Hi guys,

At a first site it might semm easy, but I couldn't find the reason for the malfunction. I create a form, dynamically. Then I ad a Frame to that form, and then I add a ComboBox to the Frame. Further I want to populate the ComboBox with a values from a Worksheet.

Let me try to bring you the relevant lines of code:


Dim wb As Workbook
Dim ip As Worksheet
Dim block as Integer

Set wb = Excel.ActiveWorkbook
Set ip = wb.Sheets("Input")

Dim MetaParList, NewComboBox As Object
Dim NewFrame1 As MSForms.Frame

Set MetaParList = Application.VBE.ActiveVBProject.VBComponents.Add(3)
Set NewFrame1 = MetaParList.designer.Controls.Add("Forms.Frame.1")
Set NewComboBox = NewFrame1.Controls.Add("Forms.ComboBox.1")

With NewComboBox
block = 2
While Not IsEmpty(ip.Cells(block, 17).Value)
.AddItem ip.Cells(block, 17).Value
.List(.ListCount - 1, 1) = ip.Cells(block, 18).Offset(0, 1).Value
block = block + 1
Wend
.Width = 450
.Height = 18
End With

VBA.UserForms.Add(MetaParList.Name).Show



Allright, I do get the form with a ComboBox, but the ComboBox is empty. What is wrong here?

Any hints will be appreciated.
Lionne

[mikerickson]

Aside from noting that on the fly userforms are buggy and shouldn't be used when a design-time UF could do the job.
Aside from that, why are you using the Designer object to create the Frame when you are only going to be tossing the Userform at the end of the session.

What happens when the .AddItem line adds a consant value (like "x") instead of reading from a sheet?

[lionne]

Hi Mikerickson

Thank you very much for quick reply.

what is "design-time UF"?

I couldn't really understand your second question. Why shouldn't I use the Designer? The goal is to have a Frame, then add some other Controls there like TextBoxes, ComboBoxes, Buttons etc.

I tried to add a constant value, nothing changes. The ComboBox is still empty.

[mikerickson]

There are two ways to use a userform in a project.

Usualy, one goes to the VB editor, inserts a userform, adds controls and writes code for those controls. This occurs during "design time", when one is designing the workbook.

Your approach is to create a userform (via code) while the workbook is being used. This is called a "run time" userform or dynamicaly creating a userform.
The major problem with run-time userforms is that the code that is needed to run the UF is difficult to assign to the various controls.

I would strongly recommend that anyone create their userforms through the VB Editor rather than by code. Unless there is an equaly (very strong) reason to do otherwise.

[lionne]

Yes, I mentioned, that the UserForm I create is a dynamically created UserForm, and I do need that one, because I have over 250 Templates with different number of Frames/TextBoxes/ComboBoxes, so it is the only way. It was however, not that difficult to assign the code to the controls. Until now it worked really smoothly, before I got the ComboBox ;)

And I can't really understand, what is wrong here. When you have a "normal" userform, you can do like

UserForm1.ComboBox.AddItem

In my case, I don't really know how to refer to the ComboBox. When I created it with

Set NewComboBox = NewFrame1.Controls.Add("Forms.ComboBox.1")

I somehow doubt, that the correct way to add items to the CB would be

NewComboBox.AddItem ip.Cells(iRow, 17).Value

, because in the first case I refer to a name of ComboBox, and in the second case to an object.

Correct me, if I am wrong.. but I stuck with it completely...

[Kenneth Hobs]

I recommend doing it manually and then adapt it. I am not sure what you wanted with the List() since you had AddItem().

e.g.
' Add: Tools > Reference > Microsoft Visual Basic for Applications Extensibility 5.3 > OK
' Add: Tools > Reference > Microsoft Forms 2.0 Object Library > OK
' http://support.microsoft.com/kb/204330
' http://forums.esri.com/Thread.asp?c=93&f=992&t=298827
Sub MakeUserform()
Dim MetaParList, NewComboBox As MSForms.ComboBox
Dim NewFrame1 As MSForms.Frame
Dim pVBComponent As VBComponent
Dim nLines As Integer

Set MetaParList = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm) 'vbext_ct_MSForm=3
Set NewFrame1 = MetaParList.Designer.Controls.Add("Forms.Frame.1")
Set NewComboBox = NewFrame1.Controls.Add("Forms.ComboBox.1")

With MetaParList
nLines = .CodeModule.CountOfLines
.CodeModule.InsertLines nLines + 1, "Private Sub UserForm_Initialize()"
.CodeModule.InsertLines nLines + 2, " Dim wb As Workbook"
.CodeModule.InsertLines nLines + 3, " Dim ip As Worksheet"
.CodeModule.InsertLines nLines + 4, " Dim block As Integer"
.CodeModule.InsertLines nLines + 5, ""
.CodeModule.InsertLines nLines + 6, " Set wb = Excel.ActiveWorkbook"
.CodeModule.InsertLines nLines + 7, " Set ip = wb.Sheets(""Input"")"
.CodeModule.InsertLines nLines + 8, " With ComboBox1"
.CodeModule.InsertLines nLines + 9, " block = 2"
.CodeModule.InsertLines nLines + 10, " While Not IsEmpty(ip.Cells(block, 17).Value)"
.CodeModule.InsertLines nLines + 11, " .AddItem ip.Cells(block, 17).Value"
.CodeModule.InsertLines nLines + 12, " .List(.ListCount - 1, 1) = ip.Cells(block, 18).Offset(0, 1).Value"
.CodeModule.InsertLines nLines + 13, " block = block + 1"
.CodeModule.InsertLines nLines + 14, " Wend"
.CodeModule.InsertLines nLines + 15, " .Width = 50"
.CodeModule.InsertLines nLines + 16, " .Height = 18"
.CodeModule.InsertLines nLines + 17, " End With"
.CodeModule.InsertLines nLines + 18, "End Sub"
End With

VBA.UserForms.Add(MetaParList.Name).Show

Set pVBComponent = ThisWorkbook.VBProject.VBComponents(MetaParList.Name)
ThisWorkbook.VBProject.VBComponents.Remove pVBComponent
End Sub

[mikerickson]

If adding the code is no problem, then the loading of the combo box can be should be put in the user form's Intialize event.

[lionne]

Dear Mike

Unfortunately, my problem with the comboboxes is still not solved yet. But I analyzed the situation further in debug mode and found some interesting news: the dynamically created comboboxes actually have the correct values in them, but when the program is executed, the combobox still shows up completely empty at runtime.

I verified the values in the combobox by using the ".List(idx)" property, idx looping over all entries. I can easily display all the values in the combobox at runtime using "msgbox cmbBox.List(idx)" which proves that the combobox is actually filled correctly. Therefore, I doubt that the issue is related to the initailization of the form. To me, it seems more like a displaying problem.

Now my question: what do I have to do in order to make this combobox actually show the values that it holds? I am completely stuck on this and really need help!

Thank you so much for your effort in looking into this again,
Lionne

[lionne]

Dear Kenneth,

thank you for your reply! Unfortunately, my code is still not working properly (please see the description I posted 5 mintes ago, addressed to Mike). Do you have any suggestions as to what could be the reason for my combobox not displaying any values (although carrying the values in them)?

Your feedback / opinion on this is highly appreciated!
Kind regards,
Lionne

[mikerickson]

Try something like this, that instansizes the UF before adding to the combo box.
'...
Set MetaParList = Application.VBE.ActiveVBProject.VBComponents.Add(3)

With VBA.UserForms.Add(MetaParList.Name)
Set NewFrame1 = .designer.Controls.Add("Forms.Frame.1")
Set NewComboBox = NewFrame1.Controls.Add("Forms.ComboBox.1")


With NewComboBox
block = 2
While Not IsEmpty(ip.Cells(block, 17).Value)
.AddItem ip.Cells(block, 17).Value
.List(.ListCount - 1, 1) = ip.Cells(block, 18).Offset(0, 1).Value
block = block + 1
Wend
.Width = 450
.Height = 18
End With

.Show
End With

A couple of questions about your overall process:
Are you deleting the newly created VBComponent when the user is done with it?
If not, does the logic expect those values to be in the ComboBox every time that user form is shown, without being re-AddItem'ed by some further code that runs when the UF is re-shown?

[lionne]

Hi Mike

thanks for the code.
Set NewFrame1 = .designer.Controls.Add("Forms.Frame.1")
didn't work, so I changed it to
Set NewFrame1 = .Controls.Add("Forms.Frame.1")
as designer Methode was not applicable here. Changing the code for other controls and executing, however, didn't help. The Form came empty without any controls on it. Besides, I couldn't find the way to set the properties of
VBA.UserForms.Add(MetaParList.Name)

Answering the questions: yes, I do delete the form, when the user is done with it executing:
ThisWorkbook.VBProject.VBComponents.Remove MetaParList

Will work further on it and let you know, as soon as I'll get the result.

[lionne]

Hi guys

I just got the solution from Snakehips on MrExcel.com

The ComboBox properties
.ColumnCount = 2
.RowSource = "'Input'!A1:B5"

make the wonder!!! No need to
.AddItem

If the range, that you want your ComboBox to have are dynamic, just Dim e.g. MyRange as dynamic range, and that's it!

I kindly appreciate Mike's and Kenneth's help, thanks guys... and hasta la vista ;)