Pass variables between a form and VBA code (Module)

[BetterBeef]

I am new to using forms in VBA and am struggling with getting my variables to transfer over to my main module (Module1). Here is the code for both my form and my module.

MODULE:

Sub Update_0930()
Dim Report As Worksheet, R1 As Worksheet, LastRow As Integer
Dim vDate As Date
Public vMon As Boolean, vTue As Boolean, vWed As Boolean, vThu As Boolean, vFri As Boolean
 
Set Report = Sheets("Sales Update")
Set R1 = Sheets("09.30 SALES REPORT")
 
'Enter Day of Week & Date
frmSelectDay_Date.Show
Report.Range("A11") = vDate
 
If vMon = True Then
Report.Range("B11") = "MON"
End If
If vTue = True Then
Report.Range("B11") = "TUE"
End If
If vWed = True Then
Report.Range("B11") = "WED"
End If
If vThu = True Then
Report.Range("B11") = "THU"
End If
If vFri = True Then
Report.Range("B11") = "FRI"
End If
 
'Transfer 3:45 PM UPDATE and move into "Yesterday's Final" column
LastRow = Application.Match("TOTALS:", Report.Range("A:A"), 0)
Report.Range(Cells(14, 9), Cells(LastRow, 9)).Copy
Report.Range(Cells(14, 5), Cells(LastRow, 5)).PasteSpecial xlValues
Application.CutCopyMode = False
Report.Range("B11").Select
 
'Clear out update info
Report.Range("G11:I11").ClearContents
Report.Range(Cells(14, 15), Cells(LastRow, 18)).ClearContents
 
'Refresh Pivot Tables in "09.30 Sales Report" Tab
R1.PivotTables("Salesman_Detail").RefreshTable
R1.PivotTables("Sales_Dept_Detail").RefreshTable
 
End Sub

FORM:

Private Sub cmdOK_Click()
'Dim vDate As Date
'Dim vMon As Boolean, vTue As Boolean, vWed As Boolean, vThu As Boolean, vFri As Boolean
Dim msg As Integer
 
If frmSelectDay_Date.txtDate = "" Then
msg = MsgBox("Please enter today's date.", vbCritical, "Please Fill Out Form")
Exit Sub
End If
 
'Set variable equal to the value of each control
vDate = frmSelectDay_Date.txtDate
vMon = frmSelectDay_Date.optMON
vTue = frmSelectDay_Date.optTUE
vWed = frmSelectDay_Date.optWED
vThu = frmSelectDay_Date.optTHU
vFri = frmSelectDay_Date.optFRI
 
If vMon = False And vTue = False And vWed = False And vThu = False And vFri = False Then
msg = MsgBox("Please select a day.", vbCritical, "Please Fill Out Form")
Exit Sub
End If
 
frmSelectDay_Date.Hide
'Exit Sub
 
End Sub

I'm not sure where I declare my variables. I am guessing that I need to declare them in the module. I just need some help getting my variables in my form to transfer over into the module. When I go from the form to the module, all the variables are reset in the debug window.

Thanks for your help!

 

[Norie]

I'm still not seeing the need for multiple forms, and I just keep on thinking that multiple forms are a bad idea.

You say this is to simplify things, but it sounds to me as though it might actually complicate things.

 

[BetterBeef]

So, is it possible to pass a variable from a form to a sub? I only want the user to see information that is specific to the criteria he/she selects, hence the purpose for multiple forms. Tell me how you would recommend putting these into one form?

 

[BetterBeef]

So I finally figured out how to do this. I thought I would post the solution for anyone who would search on this topic so that they would know how to do it as well.

What I ended up doing was instead of setting the controls in the form equal to variables in the form code, I set them equal in the sub code. This was a fairly simply solution, but for some reason it was a struggle to get to this conclusion.

Anyway, here is the new code for the sub (module). The bolded text is what I added from the previous time I added the code:

Sub Update_0930()
    Dim Report As Worksheet, R1 As Worksheet, LastRow As Integer
    Dim vDate As Date
    Dim vMon As Boolean, vTue As Boolean, vWed As Boolean, vThu As Boolean, vFri As Boolean
 
    Set Report = Sheets("Sales Update")
    Set R1 = Sheets("09.30 SALES REPORT")
 
    'Enter Day of Week & Date
    frmSelectDay_Date.Show
[B]   vDate = frmSelectDay_Date.txtDate[/B]
[B]   vMon = frmSelectDay_Date.optMON[/B]
[B]   vTue = frmSelectDay_Date.optTUE[/B]
[B]   vWed = frmSelectDay_Date.optWED[/B]
[B]   vThu = frmSelectDay_Date.optTHU[/B]
[B]   vFri = frmSelectDay_Date.optFRI[/B]
 
    Unload frmSelectDay_Date
 
    Report.Range("B11") = vDate
 
    If vMon = True Then
        Report.Range("A11") = "MON"
    End If
    If vTue = True Then
        Report.Range("A11") = "TUE"
    End If
    If vWed = True Then
        Report.Range("A11") = "WED"
    End If
    If vThu = True Then
        Report.Range("A11") = "THU"
    End If
    If vFri = True Then
        Report.Range("A11") = "FRI"
    End If
 
 
    'Transfer 3:45 PM UPDATE and move into "Yesterday's Final" column
    LastRow = Application.Match("TOTALS:", Report.Range("A:A"), 0)
    Report.Range(Cells(14, 9), Cells(LastRow, 9)).Copy
    Report.Range(Cells(14, 5), Cells(LastRow, 5)).PasteSpecial xlValues
    Application.CutCopyMode = False
    Report.Range("B11").Select
 
    'Clear out update info
    Report.Range("G11:I11").ClearContents
 
    'Refresh Pivot Tables in "09.30 Sales Report" Tab
    R1.PivotTables("Salesman_Detail").RefreshTable
    R1.PivotTables("Sales_Dept_Detail").RefreshTable
 
End Sub

By ensuring that I declare all my variables and set them equal to my form controls in the sub, I am able to pass information form controls to the module.