Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using one Input Box Method For Two Separate Macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using one Input Box Method For Two Separate Macros
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 -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using one Input Box Method For Two Separate Macros
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
An alternate input method? | Excel Discussion (Misc queries) | |||
Using input box to improve my method | Excel Programming | |||
Numerical Input via an inputbox method | Excel Programming | |||
Numerical Input via an inputbox method | Excel Programming | |||
Find method using User input | Excel Programming |