Calling a userform from a specific sheet sub


Another newbie question but I cannot find my answer anywhere so far...

I have a workbook with several sheets, lets call them S1, S2 etc., I have a userform that does an operation that can be activated from any of the sheet.

My problem here is that I have parameters passed to the userform from the sub

Public c As Integer, lf As Integer, ld As Integer
Sub Tri()
ld = 8
lf = 128    
Application.ScreenUpdating = False

End Sub

Now my workbook is growing in size and differences appear from S1 to S2 etc requiring me to change parameters depending on the sheet it is launched from. So i removed my code from "module" and put it in the "Microsoft excel object" part. But it now seems it does not have access to my public variables and as soon as I request ld or lf, it is shown as empty (even if it was implemented in the previous userform).

Please can someone tell me what I'm missing ? How can I do otherwise (I do not want to put the data in the sheets themselves)?

Show source
| excel-vba   | vba   | excel   2017-01-05 15:01 2 Answers

Answers ( 2 )

  1. 2017-01-05 16:01

    You need to take advantage of the fact that a userform is a class. So as an example add the following code to the "form". Let's assume you have a button with the name CommandButton1

    Option Explicit
    Dim mVar1 As Long
    Dim mVar2 As String
    Property Let Var1(nVal As Long)
        mVar1 = nVal
    End Property
    Property Let Var2(nVal As String)
        mVar2 = nVal
    End Property
    Private Sub CommandButton1_Click()
        MsgBox mVar1 & " - " & mVar2
    End Sub

    Then you can add in a normal Module

    Sub TestForm()
        Dim frm As UserForm1
        Set frm = New UserForm1
        Load frm
        frm.Var1 = 42
        frm.Var2 = "Test"
        Unload frm
    End Sub

    In such a way you can pass variables to a form without using global variables.

  2. 2017-01-05 16:01

    Here is a widely accepted answer about Variable Scopes.

    If you have decalred your variable inside thisworkbook, you need to access it by fully qualifying it. Like ThisWorkbook.VariableName

    But with UserForms I recommend to use Properties for data flow. Thats the clean and robust way to do it. Get in the habit of using properties and you will find it highly beneficial for UserForms.


    Add this code in the ThisWorkbook

    Option Explicit
    '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else.
    '/ Example ThisWorkbook.x
    Public x As Integer
    Sub test()
        Dim uf As New UserForm1
        x = 10
        '/ Set the propertyvalue
        uf.TestSquare = 5
        '/Show the form
        '/ Get the property value
        MsgBox "Square is (by property) : " & uf.TestSquare
        '/Get Variable
        MsgBox "Square is (by variable) : " & x
        Unload uf
    End Sub

    Now add a UserForm, called UserForm1 and add this code

    Option Explicit
    Private m_lTestSquare As Long
    Public Property Get TestSquare() As Long
        TestSquare = m_lTestSquare
    End Property
    Public Property Let TestSquare(ByVal lNewValue As Long)
        m_lTestSquare = lNewValue
    End Property
    Private Sub UserForm_Click()
        '/ Accessing the Variable Defined inside ThisWorkkbook
        ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x
        '/ Changing Property Value
        Me.TestSquare = Me.TestSquare * Me.TestSquare
    End Sub

    Now when you run the Test sub from ThisWorkbook you will see how you can access variables and properties across the code.

◀ Go back