Option Explicit
‘Two custom procedures
‘ 1. CreateUserform
‘ 2. AddControlToForm
‘ Written by Selva V Pasupathy
‘ Before you continue using this code, please be advised that
‘ a reference to microsoft visual basic applications for extensibility
‘ library is required, and also
‘ go to Tools >> Macros >> security >> Trusted Publishers(tab)
‘ and click on the checkbox with caption as
‘ “Trust Access to Visual Basic Project”
Sub CreateUserForm(Optional ByVal Name As String, _
Optional ByVal Caption As String, _
Optional ByVal Left As Double, _
Optional ByVal Top As Double, _
Optional ByVal Width As Double, _
Optional ByVal Height As Double, _
Optional ByVal BackColor As Double, _
Optional ByVal BorderStyle As Integer, _
Optional ByVal BorderColor As Double, _
Optional ByVal SpecialEffect As Integer, _
Optional ByVal showmodal As Integer, _
Optional ByVal StartUpPosition As Integer, _
Optional ByVal KeepScrollBarsVisible As Integer, _
Optional ByVal ScrollHeight As Double, _
Optional ByVal ScrollWidth As Double)
‘On Error GoTo ErrHandler
‘Locks Excel spreadsheet and speeds up form processing
Application.VBE.MainWindow.Visible = False
Application.ScreenUpdating = False
Dim newform As VBComponent
Set newform = ThisWorkbook.VBProject.VBComponents.Add(3)
With newform
.Name = Name
.Properties(“caption”) = Caption
.Properties(“left”) = Left
.Properties(“top”) = Top
.Properties(“width”) = Width
.Properties(“height”) = Height
.Properties(“backcolor”) = BackColor
.Properties(“borderstyle”) = BorderStyle
.Properties(“bordercolor”) = BorderColor
.Properties(“specialeffect”) = SpecialEffect
.Properties(“showmodal”) = showmodal
.Properties(“StartUpPosition”) = StartUpPosition
.Properties(“KeepScrollBarsVisible”) = KeepScrollBarsVisible
.Properties(“ScrollHeight”) = ScrollHeight
.Properties(“Scrollwidth”) = ScrollWidth
End With
Set newform = Nothing
Application.VBE.MainWindow.Visible = True
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox “Error: ” & Err.Number & _
vbNewLine & Err.Description
Application.VBE.MainWindow.Visible = True
Application.ScreenUpdating = True
End Sub
Sub AddControlToForm(ByVal frm As String, _
ByVal myBook As Workbook, _
ByVal ControlType As String, _
Optional ByVal oContainer As String, _
Optional ByVal scaption As String, _
Optional ByVal sText As String, _
Optional ByVal dleft As Double, _
Optional ByVal dtop As Double, _
Optional ByVal dWidth As Double, _
Optional ByVal dHeight As Double, _
Optional ByVal BackStyle As Long, _
Optional ByVal BackColor As Long, _
Optional ByVal BorderStyle As Long, _
Optional ByVal BorderColor As Long, _
Optional ByVal SpecialEffect As Integer)
‘__________________________________________________________________________
‘ – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – -
‘ myBook = workbook reference where the userform resides
‘ frm = the name of the userform as string
‘ dleft=the left position of the label to be added
‘ dtop=the topposition of the label to be added
‘ dwidth= width of the label to be added
‘ dheight = height of the label to be added
‘ backstyle = backstyle of the label to be added
‘ if backstyle=false then transparent label otherwise opaque
‘ backcolor=an integer representing the backcolor
‘ of the label to be added
‘ specialeffect of the label to be added in integer
‘ flat = 0, RAISED = 1, SUNKEN = 2, ETCHED = 3, BUMP = 6
‘__________________________________________________________________________
‘ – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – -
‘On Error GoTo ErrHandler
Dim myContainer As Control
‘Locks Excel spreadsheet and speeds up form processing
Application.VBE.MainWindow.Visible = False
Application.ScreenUpdating = False
Dim TempForm
Dim newControl As Control
Set TempForm = myBook.VBProject.VBComponents(frm)
If oContainer = “” Then
Set newControl = TempForm.Designer.Controls _
.Add(“forms.” & ControlType & “.1″)
Else
Set myContainer = TempForm.Designer.Controls(oContainer)
Set newControl = myContainer.Add(“forms.” & ControlType & “.1″)
End If
With newControl
If scaption “” Then
.Caption = scaption
End If
If sText “” Then
.Value = sText
End If
If dWidth 0 Then
.Width = dWidth
End If
If dHeight 0 Then
.Height = dHeight
End If
If dleft 0 Then
.Left = dleft
End If
If dtop 0 Then
.Top = dtop
End If
If BackStyle = -1 Then
‘default
ElseIf BackStyle = 0 Then
.BackStyle = fmBackStyleTransparent
Else
.BackStyle = fmBackStyleOpaque
End If
If BorderColor 0 Then
.BorderColor = BorderColor
End If
If BorderStyle = -1 Then
‘default
ElseIf BorderStyle = 0 Then
.BorderStyle = fmBorderStyleNone
Else
.BorderStyle = fmBorderStyleSingle
End If
If BorderColor 0 Then
.BorderColor = BorderColor
End If
If SpecialEffect 0 Then
.SpecialEffect = SpecialEffect
End If
End With
Application.VBE.MainWindow.Visible = True
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
Application.VBE.MainWindow.Visible = True
Application.ScreenUpdating = True
End Sub
