Dynamically

Creating userforms dynamically
adding controls
adding the event subroutines
moving and sizing controls


Importing a Userform

The quickest way to add a userform to a project is to just import it from a saved file.

Dim objVBC As VBComponent 
Set objVBC = Application.VBE.ActiveVBProject.VBComponents.Import("MyForm.frm")

The advantage of doing it through code is that the userform can be given a size appropriate to the user's screen resolution and size, and its controls positioned correctly.


Windows API call to freeze a window.
Private Declare Function LockWindowUpdate Lib "User32" (ByVal hwndLock As Long) As Long


Application.ScreenUpdating does not affect the VBE
You shoud use the above API function to freeze and unfreeze the window accordingly.


It is a good idea to leave 6 points between controls and between a control and the edge of the userform.


Freeze the VBE window
The HWnd property is a hidden property of the MainWindow object.

LockWindowUpdate Application.VBE.MainWindow.HWnd 

Add a new userform

Set objVBC = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm) 

set the height and width

objVBC.Properties("Width") = Application.UsableWidth * 2 / 3 
objVBC.Properties("Height") = Application.UsableHeight * 2 / 3

Adding controls

Dim objFrmDesign As ?? 
Dim objControl As ??

Set objFrmDesign = objVBC.Designer

   Set objControl = objFrmDesign.Controls.Add("Forms.CommandButton.1"),"bnOK")
   objControl.Caption = "OK"
   objControl.Default = True
   objControl.Height = 20
   objControl.Width = 20

The name of the control to add can be found by adding a control from the Control Toolbox to a worksheet and then examining the EMBED function.


Add the references "Microsoft Forms 2.0 Object Library"
Add the references "Microsoft Visual Basic for Applications Extensibility 5.3"

Dim obVBE As VBIDE.VBE 
Dim frm1 As VBIDE.VBCompionent
Dim frm2 As MsForms.Userform
Set obVBE = Application.VBE
Set frm1 = obVBE.ActiveVBProject.VBComponets.Add(vbext_ct_MSForm)
Dim cmd1 As MsForms.Control
With frm1
   .Properties("Height") = 280
End With
With frm2
   .Font.Name = "Tahoma"
End With
Set cmd1 = frm2.Controls.Add("Forms.CommandButton.1","cmbButton1")


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext