Calling a userform from a specific sheet sub

Question

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
UsForm.Show

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)?

score 1 · Accepted Answer · answered Jan 05 '17 at 15:18

1

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
    Me.Hide
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"
    frm.Show
    Unload frm
End Sub

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

answered Jan 05 '17 at 15:18

Storax

11,158
3
16
33

This seems to do the trick, I'll need to try it in my workbook. Can you however quickly remind me of the let, get, set etc. properties that I am not familiar with ? Thank you (I will validate as soon as I get it working on my workbook) – PEagle Jan 10 '17 at 10:55
As said a userform is a class. A good explanation IMHO can be found here http://excelmacromastery.com/vba-class-modules/ and here is the chapter for properties http://excelmacromastery.com/vba-class-modules/#Class_Module_Properties – Storax Jan 10 '17 at 11:30

score 1 · Answer 2 · edited May 23 '17 at 12:16

Here is a widely accepted answer about Variable Scopes. https://stackoverflow.com/a/3815797/3961708

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.

Example:

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
    uf.Show

    '/ 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

    Me.Hide

End Sub

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

Sorry for the very late answer, my work kept me away from the file. I ran the code step by step and do understand what it does. However the access to the variable defied inside this workbook part returns error : "method or data member not found" — PEagle, Jan 10 '17 at 10:51
Thank you very much for your answer on the variable scope, I now have much better comprehension on that. — PEagle, Jan 10 '17 at 13:56

Calling a userform from a specific sheet sub

2 Answers2