Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms that are generated at runtime
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
printing blank forms with auto generated numbers | Excel Worksheet Functions | |||
User Forms | Excel Discussion (Misc queries) | |||
Excel 2002 Data Forms runtime error 1004 | Excel Programming | |||
VB user forms | Excel Programming | |||
User forms | Excel Programming |