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

(mike888) wrote in message . com...
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