BetterBeef
New Member
- Joined
- Mar 18, 2010
- Messages
- 42
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:
FORM:
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!
MODULE:
Code:
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:
Code:
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!