Add Label to Userform Programmatically


Selva V Pasupathy HSBC Global Resourcing, Hyderabad

When you want to add a label or any other control programmatically, you use objects from microsoft visual basic for application extensibility. However, if you are going to automate the use of this library, it will be good if you have customized function to create userform and userform controls at runtime. I have written the following function which can be used to create label on a userform programmatically, and similarly you can change some aspects of the function to add other controls as well. I have tried using this function, and I am very comfortable in creating user interface easily by just passing on the values of the ocntrols’ properties.

Option Explicit

‘ 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”

Function AddLabelToForm(ByVal frm As String, _
                ByVal myBook As Workbook, _
                Optional ByVal oContainer As String, _
                Optional ByVal scaption 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 Boolean, _
                Optional ByVal backcolor 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
‘__________________________________________________________________________
‘   – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
  
  Dim tempform
  Dim myContainer As MSForms.Frame
  Dim newLabel As MSForms.Label
  
  Set tempform = myBook.VBProject.vbcomponents(frm)
  
  
  
  If Not IsEmpty(oContainer) And Not oContainer = “” Then
    Set myContainer = ThisWorkbook.VBProject.vbcomponents _
                    (“userform1”).designer.Controls(oContainer)
  End If
  
  If Not IsEmpty(oContainer) And Not oContainer = “” Then
    Set newLabel = myContainer.Controls _
                    .Add(“forms.Label.1”)
  Else
    Set newLabel = tempform.designer.Controls _
                    .Add(“forms.Label.1”)
  End If
  
  
  With newLabel
    If scaption “” Then
      .Caption = scaption
    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 = True Then
      .BackStyle = fmBackStyleOpaque
    Else
      .BackStyle = fmBackStyleTransparent
    End If
    
    If backcolor 0 Then
    .backcolor = backcolor
    End If
    
    If SpecialEffect 0 Then
    .SpecialEffect = SpecialEffect
    End If
  
  End With
End Function


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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: