User forms that are generated at runtime

[mike888]

Hi-

I am attempting to generate a userform dynamically from the _Click
event of a button. I have successfully generated the form with the
layout that I would like. One problem is that I cannot populate the
comboBoxes using .AddItem (I have 2 attempts in the code below), I can
however set a value using the .Value attribute. The second part of my
problem is that on the submit button of this generated form, the
values that I put into the comboBoxes get lost when I try to use them
in another function.

If I generate the comboBoxes with a .Value attribute the value is
shown on my userform. When I submit to the next function
(ReadAndDisplayFieldsSetVariableFromOSDM) the MsgBox shows this same
value.

If I generate without the .Value attribute the MsgBox in
ReadAndDisplayFieldsSetVariableFromOSDM is blank. But, if I type in
the value "Java" in the comboBox the correct logic is executed for the
userform (toggling the visibility of another comboBox).

What I would like to accomplish is to:
1) Populate the comboBoxes with values during the generation of the
userform.
2) Have the ability to choose a value from the comboBox and reference
it from another function.

Sorry for the confusing description.

Thanks in advance for all help!

-Mike

PS - Thanks to John Walkenbach's book 'Excel 2002 Power Programming
with VBA' for getting me this far!

**************** Code
Public Sub RuntimeForm()

Dim NewButton As MSForms.CommandButton
Dim Line As Integer
Dim topIncrement, topStart As Integer

' Create the UserForm
Set TempForm = ThisWorkbook.VBProject. _
VBComponents.Add(3) 'vbext_ct_MSForm
With TempForm
.properties("Caption") = "Set Variable"
.properties("Width") = 560
.properties("Height") = 450
End With


' Add Objects to form
topStart = 55
topIncrement = 25

' Labels
Set NewLabel = TempForm.Designer.Controls _
.Add("forms.Label.1")
With NewLabel
.Left = 100
.Height = 24
.Top = 10
.Width = 300
.Caption = " Enter Values for the Key Fields Below"
.Font = "Tahoma"
.FontSize = 14
.FontBold = True
End With

' Comboboxes
For i = 1 To 12

myJavaVXML = "myJavaVXML" & i

Set NewCombobox = TempForm.Designer.Controls _
.Add("forms.Combobox.1", myJavaVXML)
With NewCombobox
.Left = 10
.Height = 20
.Style = 0
.Top = topStart
.Width = 50

.AddItem ("Java")
.AddItem ("VXML")
End With

Set NewCombobox = TempForm.Designer.Controls _
.Add("forms.Combobox.1", myJavaType)
With NewCombobox
.Left = 65
.Height = 20
.Top = topStart
.Visible = False
.Width = 50
End With

NewCombobox.AddItem ("String")
NewCombobox.AddItem ("int")
NewCombobox.AddItem ("boolean")
NewCombobox.AddItem ("double")

topStart = topStart + topIncrement

Next i

' Add Buttons
Set NewButton = TempForm.Designer.Controls _
.Add("forms.CommandButton.1", "btnSubmit")
With NewButton
.FontBold = True
.Caption = "Add /Update Fields"
.Height = 24
.Left = 174
.Top = 385
.Width = 84
End With

Set NewButton = TempForm.Designer.Controls _
.Add("forms.CommandButton.1", "btnCancel")
With NewButton
.FontBold = True
.Caption = "Cancel"
.Height = 24
.Left = 260
.Top = 385
.Width = 84
End With


' Add ComboBox code

For k = 1 To 12
With TempForm.CodeModule
code = ""
code = code & "Private Sub myJavaVXML" & k & "_Change()" &
vbCr
code = code & "If myJavaVXML" & k & ".Value = ""Java""
Then" & vbCr
code = code & "myJavaType" & k & ".Visible = True" & vbCr
code = code & "Else" & vbCr
code = code & "myJavaType" & k & ".Visible = False" & vbCr
code = code & "End If" & vbCr
code = code & "End Sub"
.InsertLines .CountOfLines + 1, code
End With
Next k

' Add Button Code

With TempForm.CodeModule
code = ""
code = code & "Private Sub btnSubmit_Click()" & vbCr
code = code & "ReadAndDisplayFieldsSetVariableFromOSDM" & vbCr
code = code & "Unload Me" & vbCr
code = code & "End Sub"
.InsertLines .CountOfLines + 1, code
End With

With TempForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Sub btnCancel_Click()"
.InsertLines Line + 2, "Unload Me"
.InsertLines Line + 3, "End Sub"
End With

' Show the form
VBA.UserForms.Add(TempForm.Name).Show

'
' Delete the form
ThisWorkbook.VBProject.VBComponents.Remove TempForm

End Sub



Public Sub ReadAndDisplayFieldsSetVariableFromOSDM()

Dim myValue As String

For i = 1 To 12
myJavaVXML = "myJavaVXML" & i

' Dynamic Form
myValue = UserForm1(myJavaVXML).Value

MsgBox (myValue)
Next i

End Sub

 

[mike888]

Has anyone had a situation like this? TIA for any help.

 

[Dave Peterson]

Not an answer to your questions, but is there a reason you couldn't design the
userform with all the comboboxes you'd ever need and hide them.

Then you could decide later to show them and then add items to the comboboxes.

It might make things a lot easier.

 

[ALEKSEJ]

Hi,
Not an answer to your questions, but you could decide comboboxes
and other controls dynamically with my MX
(server:database M3-Lite <--IE--> client:excel)

ALEX

 

[mike888]

Thanks for your responses. Currently I am creating all of the
comboboxes that I need. I also determine the visibility of the second
combobox from the value in the first. My problem is that the .AddItem
function doesn't seem to work. I cannot understand why because the
size and location attributes all work and the .Value attribute works.
Alex - I am unclear on your suggestion, is there a MX class in VBA??

Thanks again everyone.

 

[Dave Peterson]

You may want to post the portion of your code that you're having trouble with.

 

[mike888]

Thanks to all for your help the answer is to add the items during the
Activate() Event of the form.

With TempForm.CodeModule
code = ""
code = code & "Private Sub UserForm_Activate()" & vbCr
For k = 1 To 12
code = code & "myJavaVXML" & k & ".AddItem (""Java"")" &
vbCr
Next k
code = code & "End Sub"
.InsertLines .CountOfLines + 1, code
End With

 

[mike888]

Thanks to all for your help the answer is to add the items during the
Activate() Event of the form.

With TempForm.CodeModule
code = ""
code = code & "Private Sub UserForm_Activate()" & vbCr
For k = 1 To 12
code = code & "myJavaVXML" & k & ".AddItem (""Java"")" &
vbCr
Next k
code = code & "End Sub"
.InsertLines .CountOfLines + 1, code
End With