Code not showing form

[gibbo1715]

Below is some amazing code by Johnske

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?

[mvidas]

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

[johnske]

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

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

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

[gibbo1715]

Thanks JOHNSKE


You are to be congratulated on a great piece of code

[johnske]

Thanks JOHNSKE


You are to be congratulated on a great piece of code

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