Dynamically created form

Dim tmpForm As VBComponent Dim tmpComboBox As MSForms.ComboBox, tmpBtn As MSForms.CommandButton Dim selExp As Long Set tmpForm = ThisWorkbook.VBProject.VBComponents.add(vbext_ct_MSForm) With tmpForm .Properties("Caption") = "Выберите пункт" End With Set tmpComboBox = tmpForm.Designer.Controls.add("Forms.ComboBox.1") With tmpComboBox .name = "tmpComboBox" End With Set tmpBtn = tmpForm.Designer.Controls.add("Forms.CommandButton.1") With tmpBtn .Caption = "Выбрать" .name = "BtnChoose" End With With tmpForm.CodeModule .InsertLines 7, "Private Sub UserForm_Activate()" .InsertLines 8, " With me.tmpComboBox" .InsertLines 9, " .AddItem 1" .InsertLines 10, " .AddItem 2" .InsertLines 11, " .ListIndex = 0" .InsertLines 12, " End With" .InsertLines 13, "End Sub" .InsertLines 20, "Private Sub BtnChoose_Click()" .InsertLines 22, "me.hide" .InsertLines 23, "End Sub" End With VBA.UserForms.add(tmpForm.name).Show ' Вот тут нужно как-то получить выбранное значение tmpComboBox ThisWorkbook.VBProject.VBComponents.remove tmpForm Set tmpComboBox = Nothing Set tmpBtn = Nothing Set tmpForm = Nothing

How can I get the selected tmpComboBox value? I tried to contact tmpComboBox directly, but after running the temporary form, it doesn’t refer to anything. I tried to create a Public variable in the form - I don’t know how to access it, for any request I get an error that tmpForm does not contain such a property. I wanted to refer to the Controls form family, but it does not have such a family.

  • The form is created and opened. Still did not choose anything. After selecting, start a new procedure that reacts to a change in the tmpComboBox value. But in general - why not create the form right away and open the finished one? - vikttur pm
  • As a result, @vikttur is made with a separate form, but the interest is with dynamically created. I supplemented the question with my actions that did not lead to the desired result. - Edward Izmalkov
  • You have opened the form and immediately want to get the value of the ComboBox . But there is no value yet! It will appear after selecting from the dropdown list. tmpComboBox.Value or tmpComboBox.Text . But not in the form creation procedure. - vikttur pm
  • @vikttur here you are wrong, the form is launched in the VBA.UserForms.add(tmpForm.name).Show and takes control. The program gets to the point where I want to get the value only after the form returns control. In my case, this is done by pressing a button. In the form, in principle, you can make it so that the value of tmpComboBox saved in some Public variable declared in the module, but I want to do without them. - Edward Izmalkov

1 answer 1

Everything turned out to be simple. You need to find this form in the VBA.UserForms collection, in this case it will already have all the properties of ordinary forms.

 For i = 0 To VBA.UserForms.Count - 1 If VBA.UserForms(i).Name = tmpForm.Name Then selExp = VBA.UserForms(i).choosenValue End If Next i

You can add to it a property in which to save and from which to get the desired value.

  .InsertLines 2, "Private formVar as Long" .InsertLines 3, "Property Get choosenValue() as Long" .InsertLines 4, " choosenValue = formVar" .InsertLines 5, "End Property"

The final code looks like this.

 Dim tmpForm As VBComponent Dim tmpComboBox As MSForms.ComboBox, tmpBtn As MSForms.CommandButton Dim selExp As Long, i As Long Set tmpForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm) With tmpForm .Properties("Caption") = "Выберите пункт" End With Set tmpComboBox = tmpForm.Designer.Controls.Add("Forms.ComboBox.1") With tmpComboBox .Name = "tmpComboBox" .Top = 10 End With Set tmpBtn = tmpForm.Designer.Controls.Add("Forms.CommandButton.1") With tmpBtn .Caption = "Выбрать" .Name = "BtnChoose" .Top = 40 End With With tmpForm.CodeModule .InsertLines 2, "Private formVar as Long" .InsertLines 3, "Property Get choosenValue() as Long" .InsertLines 4, " choosenValue = formVar" .InsertLines 5, "End Property" .InsertLines 7, "Private Sub UserForm_Activate()" .InsertLines 8, " With me.tmpComboBox" .InsertLines 9, " .AddItem 1" .InsertLines 10, " .AddItem 2" .InsertLines 11, " .ListIndex = 0" .InsertLines 12, " End With" .InsertLines 13, "End Sub" .InsertLines 20, "Private Sub BtnChoose_Click()" .InsertLines 21, " formVar = me.tmpComboBox.value" .InsertLines 22, " me.hide" .InsertLines 23, "End Sub" End With VBA.UserForms.Add(tmpForm.Name).Show For i = 0 To VBA.UserForms.Count - 1 If VBA.UserForms(i).Name = tmpForm.Name Then selExp = VBA.UserForms(i).choosenValue End If Next i ThisWorkbook.VBProject.VBComponents.Remove tmpForm Set tmpComboBox = Nothing Set tmpBtn = Nothing Set tmpForm = Nothing