Creating userforms dynamically
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.
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
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
.Properties("Height") = 280
.Font.Name = "Tahoma"
Set cmd1 = frm2.Controls.Add("Forms.CommandButton.1","cmbButton1")
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext