CODEWITHSUNDEEP
vbaexcel
PEagle
PEagle

Reputation: 35

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

Upvotes: 1

Views: 968

Answers (2)

cyboashu
cyboashu

Reputation: 10433

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.

Upvotes: 1

Storax
Storax

Reputation: 12167

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.

Upvotes: 1

Related Questions