Using one Input Box Method For Two Separate Macros

[tdboutte]

Greetings,


I currently have one Excel workbook with two separate, but similar
macros, outputting data to two separate ,but similar, worksheets based
on a date the user enters in the input box triggered by each
procedure.

I am attempting to stream line this process: one input box to capture
a date, execute the two macros, and output the results to a single
worksheet.

I created a new macro that clears the composite worksheet, evokes the
input box, and then runs the two original macros.

QUESTION: How do I get the Input Box value to carry/store for use
with Macro X and Macro Y? My Goal is to discontinue user interacting
with two separate dialog boxes which were associated with the original
macros. I'd like one Input Box, capture the date, and use that date
entry in both Macro X and Macro Y.

Any help would be much appreciated!

Cheers,
tdb

Here is the rewritten macro (Currently, the macro runs, but I do not
get any output, so the date entered in input box is being dropped
somewhere)

'Clears the output ranges in the prod plan summary sheet
Sheets("CombinedX&Y").Select
Range("A7:A300").Select
Selection.ClearContents
Range("D7:R300").Select
Selection.ClearContents

Dim BeginDate As Date

'InputBox to replace MS Excel 5 Dialog Box
BeginDate = Application.InputBox(Prompt:="Enter Begin Date As mm/
dd/yy", _
Title:="BEGIN DATE", Default:="", Type:=1)

'If user cancels the event autocalc turned on and ends macro
If BeginDate = False Then
MsgBox "Operation Cancelled"
Exit Sub
End If

'Format BeginDate input to resolve data type mismatch error
BeginDate = Format(BeginDate, "Short Date")

''Run Macro X
Application.Run "ExcelFile1.xls'!Module4.MacroX"

'Run Macro Y
Application.Run "ExcelFile1.xls'!Module1.MacroY"
End Sub

 

[John Bundy]

Try creating a global variable, such as Dim myAnswer as String above the
beginning SUB, there it can be used by all when a selection is made in the
input box set myAnswer = to the input and then you can use it anywhere

 

[Jon Peltier]

Rather than using a global variable, which can be changed at the whim of any
process that wants to wreak a little havoc, make the data an argument to the
other macros:

Sub MacroX(dtDate As Date)
' perform magic using dtDate
End Sub

Call it like this:

Application.Run "ExcelFile1.xls'!Module4.MacroX", BeginDate

- Jon