VBA TIPS
Making similar Userforms as instance from one Userform

When I would like to use some Userforms at the same time and if those forms are similar, I don't want to make many similar userforms in Visual Basic Editor. In this case I can make similar userforms as instances from one Userform.

For example, make an Userform named Userform1with following controls.
  • Label1
  • TextBox1
  • CommandBotton1




The following code will make 3 Userforms from an Userform with using The New keyword. When making instance Userforms, preserving index numbers in the Tag property, you can use that index number when you will execute the procedure written in the Userform.

Option Explicit


'Place this procedure in the standard module
Sub Show_Forms()
    Dim objForm As Object
    Dim i As Long
    Dim lngCnt As Integer
    Dim sngTop As Single
    Dim sngLeft As Single
    Dim objForms() As UserForm1

    For i = 1 To 3
        lngCnt = lngCnt + 1
        ReDim Preserve objForms(lngCnt)
        Set objForms(lngCnt) = New UserForm1
        With objForms(lngCnt)
    'Setting captions of controls
            .Caption = "Instance Number " & lngCnt
            .Controls("Label1").Caption = "Input the value " & lngCnt
            .Controls("commandbutton1").Caption = _
            "Send this text to the Cell A" & lngCnt

    ' use TAG property for preserving index
            .Tag = lngCnt
            .StartUpPosition = 0
            .Show False
            .Top = sngTop
        End With
        sngTop = sngTop + objForms(lngCnt).Height
    Next
End Sub

Option Explicit
'Place this procedure in the Userform module


Private Sub CommandButton1_Click()
    Dim varRow As Variant
    varRow = Me.Tag
    Cells(varRow, 1).Value = TextBox1.Text
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo