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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms that are generated at runtime
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms that are generated at runtime
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 ----------------- -- ALEKSEJ ------------------------------------------------------------------------ ALEKSEJ's Profile: http://www.excelforum.com/member.php...o&userid=15015 View this thread: http://www.excelforum.com/showthread...hreadid=267749 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms that are generated at runtime
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms that are generated at runtime
You may want to post the portion of your code that you're having trouble with.
mike888 wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms that are generated at runtime
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms that are generated at runtime
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 |
Reply |
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 |