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]

Are you sure you need to declare any variables?

Are you even sure you need/want 2 separate routines?

Couldn't you just put the code from the main module into the userform module? Apart from the part that shows the form of course.

The only reason I can think of for having the code in a standard module would be if it was being used somewhere else. Is it?

 

[BetterBeef]

Yeah, as I said, I'm fairly new to this. I would not be using this code anywhere else, but I don't know how to put the code all into a single module and have everything work correctly. The form's name is frmSelectDay_Date.

How would you suggest I do this?

 

[Norie]

Put the code in the userform module not a standard module.

The only thing that should be in the standard module should probably be the one line that is used to show the userform.

The rest of the code in that module can probably be moved to the userform's module, probably after the line of code that hides the form.

It might need a little bit of a tweak, for example all the declarations should be moved to the top.

Declaring mid code is not a good idea, it can be done but it makes things hard to follow.

You might also want to reconsider if option buttons are the best controls to use.

Perhaps you could use a listbox or combobox that list the days.

With the former you could actually set the properties to display each item with an option style circle thingy, that's a technical term of course.

Also do you really need the user to enter today's date? VBA has a function Date which will return today's date.

 

[BetterBeef]

I will be using mutiple forms in my module. Can I still do this the way you are suggesting?

 

[Norie]

What do you mean by that?

You said you wouldn't be using the code anywhere else, but now you appear to be saying you will.

Am I missing something?

PS Multiple forms might not be the best idea, how about trying a multipage control?

 

[BetterBeef]

Yeah, I didn't realize what you were saying. Depending on the inputs, different forms will show up for the user to enter more data. It would be very difficult to use one form for this.

I know it is more difficult, and there are more ways around it, but is it possible to pass variables from a form to a module? I'd really like to do it this way if possible.

Thanks.

 

[Norie]

I really must be missing something.

In theory you could pass data from a userform to a sub in a standard module but it wouldn't be particularly straightforward.

Especially when it appears you want to pass data from the userform to the sub that shows the userform.

When you show the userform code controls passes to it.

To pass control back to the calling sub you would need to unload the userform and when you do that you would lose all the data on it.

What in words are you trying to do and why do you need/want multiple forms?

There are probably other approachs, perhaps the multipage I mentioned.

 

[BetterBeef]

OK, I will try to explain this the best I can...

I have a spreadsheet that has to be updated 3 times a day. There are different tasks that have to be done depending on the time of day that the report is run, and what day the report is run (primarily Monday).

This is one of three forms that I will be using. The form that I am using pops up ONLY if it is the first time the report is run. When the information is entered, it then needs to be copied into the corresponding cells in the spreadsheet. The form is needed to make the process less complicated. Other forms will include entering the weekly goals for each week (done on Monday, first report) and selecting which report is to be run.

I want an independent form to come up as I would think it would be "cleaner looking" as I do not want the user to be able to select other forms, as I think they would be able to do if a multi-page control.

Again, I am new to this, and maybe what I'm assuming is wrong. Let me know your thoughts.

Thanks.

 

[BetterBeef]

I also forgot to mention, that I don't want to use VBA's equivalent of the NOW() function, since a person may miss a day and may need to type it in manually.