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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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
76,303
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
76,303
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!
 

Forum statistics

Threads
1,140,937
Messages
5,703,264
Members
421,289
Latest member
fbohlandt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top