Using VBA CommandButton Value Variable From Userform to Module

Question

I know that if we name a variable as Public then the variable could keep its value if he passed End Sub between the subs. But my promlem is that I have a variable that gets its value from commandnbutton in a userform, and I get compile error every time I try to set this variable out of the userform sub. I have a listbox of months and after selecting month you press the commandbutton and the macro starts. I know that the value of the variable will move between subs just if H passed Ens Sub,I need to use this variable value in other subs too and I need it to be dynamic, anyone knows maybe what can help me?

this is the userform sub:

 Public MainWB  As Workbook
    Public VisualWB As Workbook
    Public VisualWS As Worksheet
    Public VacationWS As Worksheet
    Public HealingWS As Worksheet
    Public IllnessWS As Worksheet
    Public Lists As Worksheet
    Public MonthCol As Long
    Public MonthName As String
    Public MonthBefore As Long
    Public ColumnSpace As Long
    Public LR As Long
    Public LC As Long
    Public myExtension As String
    Public SecondPath As String
    Public Table As Range
    Public Names As Range

    Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual


    Call initialize

    'Here I have a lot of Code

    Unload UserForm1
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
    End Sub

this is the module sub:

Option Explicit
Sub initialize()
'The variables will keep their value on all the subs

MonthName = ListOfMonths.Value
Set MainWB = ThisWorkbook
Set VacationWS = MainWB.Worksheets(1)
Set HealingWS = MainWB.Worksheets(2)
Set IllnessWS = MainWB.Worksheets(3)
Set Lists = MainWB.Worksheets("Lists")
Set VisualWS = MainWB.Worksheets("Visual")

With VisualWS
    .Range("L1:W1").Find(MonthName, , xlValues, xlWhole).Activate
End With

MonthCol = ActiveCell.Column
MonthBefore = MonthCol - 1
End Sub

When a user form is unloaded, the variables used are all wiped so you can't get the values. Try to put the variable in a module. — newacc2240
– newacc2240, Commented Nov 23, 2017 at 6:51
@newacc2240 But the variable is already in a module and it still not wotk. — Rafael Osipov
– Rafael Osipov, Commented Nov 23, 2017 at 7:02
Your public variables must be declared in a standard module, not in the code file behind the userform. — Kostas K.
– Kostas K., Commented Nov 23, 2017 at 7:11
If my answer was correct, please mark as accepted so other people know the question is solved. Have a nice day and good luck coding! — Lucas Raphael Pianegonda
– Lucas Raphael Pianegonda, Commented Nov 23, 2017 at 7:49
@LucasRaphaelPianegonda Im sorry but your answer was not working. — Rafael Osipov
– Rafael Osipov, Commented Nov 23, 2017 at 8:14

Lucas Raphael Pianegonda · Accepted Answer · 2017-11-23 10:39:29Z

My initial answer was on the right way. There is the line with monthname=listofmonths.value which is problematic because the compiler doesn't know what listofmonths is. Then the rest has to be addressed as a property of the userform using userformname.property so adjust this and then please clarify what listofmonths is and where we get is from. Without the issue line it should compile now.

Option Explicit
Sub initialize()
'The variables will keep their value on all the subs

'UserForm1.MonthName = Listofmonths.Value ' this is a issue since listofmonths is not defined
Set UserForm1.MainWB = ThisWorkbook
Set UserForm1.VacationWS = UserForm1.MainWB.Worksheets(1)
Set UserForm1.HealingWS = UserForm1.MainWB.Worksheets(2)
Set UserForm1.IllnessWS = UserForm1.MainWB.Worksheets(3)
Set UserForm1.Lists = UserForm1.MainWB.Worksheets("Lists")
Set UserForm1.VisualWS = UserForm1.MainWB.Worksheets("Visual")

With UserForm1.VisualWS
    .Range("L1:W1").Find(UserForm1.MonthName, , xlValues, xlWhole).Activate
End With

UserForm1.MonthCol = ActiveCell.Column
UserForm1.MonthBefore = UserForm1.MonthCol - 1
End Sub

Collectives™ on Stack Overflow

Using VBA CommandButton Value Variable From Userform to Module

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related