How do I use user input from different module?

TB

Board Regular
Joined
Jul 18, 2005
Messages
68
I have users input a date in one of my modules. Then later (in another module) I want to be able to use that date to calculate a new date. Is there a way for me to use the original user input (date) in another module without having the user input the date a second time? I can't seem to get it to work!

Thanks!
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Declare it as a Public variable e,g.

Public MyTime as Date
 

TB

Board Regular
Joined
Jul 18, 2005
Messages
68
When I declare it public, it does not work. I declared it public in the General Declarations section of the project. Is there something else that I need to do that I am missing?

Thanks for the help!
 

TB

Board Regular
Joined
Jul 18, 2005
Messages
68
When I run the subs, they indicate no value for the variable. The variable only appears to have a value within the sub that the user has input the value.

Does anyone else have any suggestions?

Thanks!
 

TB

Board Regular
Joined
Jul 18, 2005
Messages
68

ADVERTISEMENT

This posting is a few weeks old but I am having the same problem. Are there any other suggestions out there?

I am trying to avoid having the user input the same data twice to be used within the same module/macro.

Thanks! :biggrin:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
Could you post your current code?

I don't see why VoG's suggestion won't work.
 

TB

Board Regular
Joined
Jul 18, 2005
Messages
68

ADVERTISEMENT

Below are the two subs that I need to work together. The first sub is rather long so I am attaching an appended version below. Essentially, I need to name the final file (in the second sub) using the "DataDate" that is input by the user in the first sub. There are actually several subs that go in between these two but they do not need/access the "DataDate" and are all running properly.

Sub 1:

Dim DataDate As String
Dim Year As Integer
Dim ValidDate As Boolean
Dim PrevDate
Dim Month
Dim Response


'Input CURRENT data date and validate input format
Line30: Sheets("Data Input Control").Select
Do

DataDate = InputBox("Enter the CURRENT data month in the form MMM-YY", "CURRENT Data Month Input", Format(Date, "mmm-yy"))
If DataDate = "" Then MsgBox ("No valid date entered.") Else GoTo Line60
GoTo Line99 'Line99 ends the macro

Line60: Select Case UCase(Left(DataDate, 3))
Case "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"
Year = IIf(IsNumeric(Right(DataDate, 2)), Right(DataDate, 2), 0)
ValidDate = Year > 0 And Year < 99
Case Else
ValidDate = False
End Select

Loop Until ValidDate
'Have user validate their input
Response = MsgBox(DataDate, 4, "CURRENT Data Month?")
If Response = vbYes Then GoTo Line40 Else GoTo Line30




Sub 2:
Finalchanges()
' Finalchanges Sub
'SAVE WORKBOOK

ActiveWorkbook.SaveAs Filename:= _
"C:\My Documents\Store\" + DataDate + "\Test File " + DataDate + ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub
 

TB

Board Regular
Joined
Jul 18, 2005
Messages
68
Oh, yeah. One more thing. What I call the "Parent Sub" that gets these all going is simply:

Sub Parent()

Sub1
Sub2
MsgBox ("Macro has ended.")

End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
I'm a little confused.

Have you declared DataDate twice?

Once as a public variable and again in Sub 1?

If you have that could be the problem.
 

TB

Board Regular
Joined
Jul 18, 2005
Messages
68
That was it! You are an absolute genius and I am absolutely brain dead. This is a great message board!!

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,846
Messages
5,544,634
Members
410,626
Latest member
rkmadasu
Top