Create UserForm usign VBA

[Michael Beckinsale]

Hi All,

I wondered if it is possible to create and delete a userform using VBA code.

If it is possible can some body give me sample code please ?

Many thanks in advance.

Regards

Michael Beckinsale.

 

[Bob Phillips]

Michael,

Here is some code previously posted by John Walkenbach that should give you
a good start

1. Create a UserForm
2. Add a CommandButton to the form
3. Insert an event-handler subroutine into the code module for the UserForm
4. Show the form
5. Delete the UserForm after it's dismissed

Option Explicit
'
Sub MakeForm()
Dim TempForm As VBComponent
Dim FormName As String
Dim NewButton As Msforms.CommandButton
Dim TextLocation As Integer
'
' Create the UserForm
Set TempForm = ThisWorkbook.VBProject. _
VBComponents.Add(vbext_ct_MSForm)
FormName = TempForm.Name
With TempForm
.Properties("Caption") = "Temporary Form"
.Properties("Width") = 200
.Properties("Height") = 100
End With
'
' Add a CommandButton
Set NewButton = TempForm.Designer.Controls _
.Add("forms.CommandButton.1")
With NewButton
.Caption = "Click Me"
.Left = 60
.Top = 40
End With
'
' Add an event-hander sub for the CommandButton
With TempForm.CodeModule
TextLocation = .CreateEventProc("Click", "CommandButton1")
.InsertLines TextLocation + 1, "MsgBox ""Hello!"""
.InsertLines TextLocation + 2, "Unload Me"
End With
'
' Show the form
VBA.UserForms.Add(FormName).Show
'
' Delete the form
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

See John Walkenbach's example:

http://j-walk.com/ss/excel/tips/tip76.htm

 

[Michael Beckinsale]

Tom / Bob,

Many thanks, thats exactly what l wanted !

Regards