CODEWITHSUNDEEP
excelvbauserform
G.M
G.M

Reputation: 365

Use Variable from Useform in different Module

I couldn't quite find what I'm looking for but maybe you can help me anyway.

My problem is that I have a userform where the user has to make an input. I want to store that input and use it later in a different module i.e. paste it into a cell. The simple solution should be to just make it a public variable, but for some reason it won't work. Here is the code I tried to use:

Userform:

Option Explicit

Public VarBezeichnungReifenliste As String

Private Sub CommandButton3_Click()

    VarBezeichnungReifenliste = TextBox1.Value
    Call Übertragen

End Sub

Private Sub CommandButton2_Click()

    Unload Me

End Sub

Module:

Option Explicit

Public Sub Übertragen()

  Worksheets("XY").Cells(1, 1).Value = VarBezeichnungReifenliste

End Sub

The error message says the variable is not declared (VarBezeichnungReifenliste) so i guess I didn't declare it publicly enough?

The userform itself is opened via a simple button on the worksheet using Userform1.Show. So nothing fancy here.

Upvotes: 1

Views: 1116

Answers (2)

VBasic2008
VBasic2008

Reputation: 54948

Publicly Enough

Solution1

UserForm1:

Option Explicit

Private Sub CommandButton3_Click()
    VarBezeichnungReifenliste = TextBox1.Value
    Module1.Übertragen
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Module1:

Option Explicit

Public VarBezeichnungReifenliste As String

Sub Übertragen()
    Worksheets("XY").Cells(1, 1).Value = VarBezeichnungReifenliste
End Sub

Conclusion

Just move the variable declaration

Public VarBezeichnungReifenliste As String

to a 'normal' module.

Solution2

UserForm1:

Option Explicit

Public VarBezeichnungReifenliste As String

Private Sub CommandButton3_Click()
    VarBezeichnungReifenliste = TextBox1.Value
    Module1.Übertragen
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Module1:

Option Explicit

Sub Übertragen()
    Worksheets("XY").Cells(1, 1).Value = UserForm1.VarBezeichnungReifenliste
    Worksheets("XY").Cells(1, 1).Select
End Sub

Conclusion

Just use

Worksheets("XY").Cells(1, 1).Value = UserForm1.VarBezeichnungReifenliste

instead of

Worksheets("XY").Cells(1, 1).Value = VarBezeichnungReifenliste

in Module1.

Solution3

UserForm1:

Option Explicit

Public VarBezeichnungReifenliste As String

Private Sub CommandButton3_Click()
    VarBezeichnungReifenliste = TextBox1.Value
    Übertragen
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Sub Übertragen()
    Worksheets("XY").Cells(1, 1).Value = VarBezeichnungReifenliste
End Sub

Conclusion

Move everything into UserForm1.

Upvotes: 2

Josh Eller
Josh Eller

Reputation: 2065

VBA is weird about storing variables over the long-term. As a general rule of thumb, if you're able to interact with Excel workbooks in between a variable being saved and when you need to get the value, you can't count on that variable still holding its value.

The safest way to get around this is to just store your value in a cell of a hidden worksheet, and read it from there when you need it.

Upvotes: 0

Related Questions