gibbo1715
01-26-2005, 11:19 AM
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?
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?