Code not showing form

Below is some amazing code by Johnske

Code:

---

Sub MakeUserForm()
 Dim MyUserForm As VBComponent
 Dim NewOptionButton As Msforms.OptionButton
 Dim NewCommandButton1 As Msforms.CommandButton
 Dim NewCommandButton2 As Msforms.CommandButton
 Dim MyComboBox As Msforms.ComboBox
 Dim N As Integer, MaxWidth As Long
 '<< FIRST CHECK THAT THIS USERFORM DOESN'T ALREADY EXIST >>
 For N = 1 To ActiveWorkbook.VBProject.VBComponents.Count
 If ActiveWorkbook.VBProject.VBComponents(N). _
 Name = "NewForm" Then
 '//(If it exists, show the existing form)
 'NewForm.Show
 ShowForm
 Exit Sub
 End If
 Next N
 '<< THERE IS NO EXISTING FORM, SO MAKE ONE >>
 Set MyUserForm = ActiveWorkbook.VBProject _
 .VBComponents.Add(vbext_ct_MSForm)
 '//set the form properties
 With MyUserForm
 .Properties("Height") = 100
 .Properties("Width") = 200
 On Error Resume Next
 .Name = "NewForm"
 .Properties("Caption") = "Here is your user form"
 End With
 '<< ADD A BUTTON TO THE FORM >>
 Set NewCommandButton1 = MyUserForm.Designer. _
 Controls.Add("forms.CommandButton.1")
 '//set the button properties
 With NewCommandButton1
 .Caption = "Cancel"
 .Height = 18
 .Width = 44
 .Left = MaxWidth + 147
 .Top = 6
 End With
 '<< ADD AN OK BUTTON TO THE FORM >>
 Set NewCommandButton2 = MyUserForm.Designer. _
 Controls.Add("forms.CommandButton.1")
 '//set the ok button properties
 With NewCommandButton2
 .Caption = "OK"
 .Height = 18
 .Width = 44
 .Left = MaxWidth + 147
 .Top = 28
 End With
 '<< ADD CODE IN THE USERFORM MODULE >>
 With MyUserForm.CodeModule
 N = .CountOfLines
 .InsertLines N + 1, "Sub CommandButton1_Click()"
 .InsertLines N + 2, " Unload Me"
 .InsertLines N + 3, "End Sub"
 .InsertLines N + 4, ""
 .InsertLines N + 5, "Sub CommandButton2_Click()"
 .InsertLines N + 6, " Unload Me"
 .InsertLines N + 7, "End Sub"
 End With
 '<< ADD A COMBO BOX TO THE FORM >>
 Set MyComboBox = MyUserForm.Designer. _
 Controls.Add("Forms.ComboBox.1")
 '//set the ComboBox properties
 With MyComboBox
 .Name = "Combo1"
 .Left = 10
 .Top = 10
 .Height = 16
 .Width = 100
 End With
 ShowForm
 End Sub

---

It works great except i cant get it to show the form once it has created it, any Ideas?

Hi gibbo,
What does your "ShowForm" code look like?
Matt

Quote:

---

Originally Posted by mvidas

Hi gibbo,
What does your "ShowForm" code look like?
Matt

---

My :oops: ... Sorry, gibbo, it was late at night when I did that up and I omitted it, try this:[vba]Option Explicit

Code:

---

Sub MakeUserForm()
Dim MyUserForm As VBComponent
      Dim NewOptionButton As Msforms.OptionButton
      Dim NewCommandButton1 As Msforms.CommandButton
      Dim NewCommandButton2 As Msforms.CommandButton
      Dim MyComboBox As Msforms.ComboBox
      Dim N As Integer, MaxWidth As Long
'<< FIRST CHECK THAT THIS USERFORM DOESN'T ALREADY EXIST >>
      For N = 1 To ActiveWorkbook.VBProject.VBComponents.Count
            If ActiveWorkbook.VBProject.VBComponents(N). _
              Name = "NewForm" Then
                  '//(If it exists, show the existing form)
                  Run ("ShowForm")
                  Exit Sub
            End If
      Next N
'<< THERE IS NO EXISTING FORM, SO MAKE ONE >>
      Set MyUserForm = ActiveWorkbook.VBProject _
                      .VBComponents.Add(vbext_ct_MSForm)
      '//set the form properties
      With MyUserForm
            .Properties("Height") = 100
            .Properties("Width") = 200
            On Error Resume Next
            .Name = "NewForm"
            .Properties("Caption") = "Here is your user form"
      End With
'<< ADD A BUTTON TO THE FORM >>
      Set NewCommandButton1 = MyUserForm.Designer. _
                              Controls.Add("forms.CommandButton.1")
      '//set the button properties
      With NewCommandButton1
            .Caption = "Cancel"
            .Height = 18
            .Width = 44
            .Left = MaxWidth + 147
            .Top = 6
      End With
'<< ADD AN OK BUTTON TO THE FORM >>
      Set NewCommandButton2 = MyUserForm.Designer. _
                              Controls.Add("forms.CommandButton.1")
      '//set the ok button properties
      With NewCommandButton2
            .Caption = "OK"
            .Height = 18
            .Width = 44
            .Left = MaxWidth + 147
            .Top = 28
      End With
'<< ADD CODE IN THE USERFORM MODULE >>
      With MyUserForm.CodeModule
            N = .CountOfLines
            .InsertLines N + 1, "Sub CommandButton1_Click()"
            .InsertLines N + 2, "    Unload Me"
            .InsertLines N + 3, "End Sub"
            .InsertLines N + 4, ""
            .InsertLines N + 5, "Sub CommandButton2_Click()"
            .InsertLines N + 6, "    Unload Me"
            .InsertLines N + 7, "End Sub"
      End With
'<< ADD A COMBO BOX TO THE FORM >>
      Set MyComboBox = MyUserForm.Designer. _
                      Controls.Add("Forms.ComboBox.1")
      '//set the ComboBox properties
      With MyComboBox
            .Name = "Combo1"
            .Left = 10
            .Top = 10
            .Height = 16
            .Width = 100
      End With
            Run ("ShowForm")
End Sub

Sub ShowForm()
NewForm.Show
End Sub

---

Thanks JOHNSKE


You are to be congratulated on a great piece of code:thumb

Quote:

---

Originally Posted by gibbo1715

Thanks JOHNSKE


You are to be congratulated on a great piece of code:thumb

---

Thanx very much for that gibbo, have you checked out the VBAX Knowledge base? There's literally HEAPS of fantastic stuff by some great coders in there... Click here :yes