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?