CODEWITHSUNDEEP
vbaexceluserform
Rafael Osipov
Rafael Osipov

Reputation: 740

Using VBA CommandButton Value Variable From Userform to Module

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

Upvotes: 1

Views: 1494

Answers (1)

Lucas Raphael Pianegonda
Lucas Raphael Pianegonda

Reputation: 1181

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

Upvotes: 1

Related Questions