Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Single Message Box for chained macros
I have several macros which are all to do with file generations. They all
begin with a message box asking for a date. The resulting filenames depend on the date entered, e.g. "macro1 02/02/04", "macro2 02/02/04". I have written a master macro chaining these macros together. The hassle is when the master macro is run, I'd be prompted by the various message boxes which belong to the individual macros. Is there anyway I can put some code in the master macro so that when the message boxes come up, they'd all get the same date? Obviously I don't want to change the individual macros because they are still needed as standalone macros from time to time. TIA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Single Message Box for chained macros
Shatin,
Is it a message box?? I kinda doubt it because you can't input data to a message box. Is it an InputBox or a UserForm?? Without seeing the code, it's difficult to answer your question. What you could do is set a public variable in a regular module and populate it with the date from any of your ??boxes. Then you could check for that date. For an InputBox, just use an IF statement before it. If IsDate(MyDate) = false Then ' show my input box End If Or if it's a text box on a UserForm...... In the Activate Event. If IsDate(MyDate) Then TextBox1 = MyDate End If John "Shatin" wrote in message ... I have several macros which are all to do with file generations. They all begin with a message box asking for a date. The resulting filenames depend on the date entered, e.g. "macro1 02/02/04", "macro2 02/02/04". I have written a master macro chaining these macros together. The hassle is when the master macro is run, I'd be prompted by the various message boxes which belong to the individual macros. Is there anyway I can put some code in the master macro so that when the message boxes come up, they'd all get the same date? Obviously I don't want to change the individual macros because they are still needed as standalone macros from time to time. TIA. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Single Message Box for chained macros
Oops, you are right. I mean Input Box.
"John Wilson" wrote in message ... Shatin, Is it a message box?? I kinda doubt it because you can't input data to a message box. Is it an InputBox or a UserForm?? Without seeing the code, it's difficult to answer your question. What you could do is set a public variable in a regular module and populate it with the date from any of your ??boxes. Then you could check for that date. For an InputBox, just use an IF statement before it. If IsDate(MyDate) = false Then ' show my input box End If Or if it's a text box on a UserForm...... In the Activate Event. If IsDate(MyDate) Then TextBox1 = MyDate End If John "Shatin" wrote in message ... I have several macros which are all to do with file generations. They all begin with a message box asking for a date. The resulting filenames depend on the date entered, e.g. "macro1 02/02/04", "macro2 02/02/04". I have written a master macro chaining these macros together. The hassle is when the master macro is run, I'd be prompted by the various message boxes which belong to the individual macros. Is there anyway I can put some code in the master macro so that when the message boxes come up, they'd all get the same date? Obviously I don't want to change the individual macros because they are still needed as standalone macros from time to time. TIA. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Single Message Box for chained macros
Shatin,
You could use the ideas that I gave you before or play around with something like this: Public MyVal As String Sub TestMe() MyVal = "12/22/04" If IsDate(MyVal) Range("A1") = InputBox(prompt:="Message", Title:="Title", Default:=MyVal) Else ' your original InputBox (without the date being populated) End If End Sub With the above, set MyVal from each of the InputBoxes. If there's a date in that variable, it'll use it and if not, it'll have you fill it in. As an afterthought, if MyVal is nothing, you don't even need the IF statement Public MyVal as String Sub TestMe2() Range("A1") = InputBox(prompt:="Message", Title:="Title", Default:=MyVal) End Sub You might want to reset MyVal at some point as it'll be there as long as the workbook is opened (maybe at the very end of the last macro)?? John "Shatin" wrote in message ... Oops, you are right. I mean Input Box. "John Wilson" wrote in message ... Shatin, Is it a message box?? I kinda doubt it because you can't input data to a message box. Is it an InputBox or a UserForm?? Without seeing the code, it's difficult to answer your question. What you could do is set a public variable in a regular module and populate it with the date from any of your ??boxes. Then you could check for that date. For an InputBox, just use an IF statement before it. If IsDate(MyDate) = false Then ' show my input box End If Or if it's a text box on a UserForm...... In the Activate Event. If IsDate(MyDate) Then TextBox1 = MyDate End If John "Shatin" wrote in message ... I have several macros which are all to do with file generations. They all begin with a message box asking for a date. The resulting filenames depend on the date entered, e.g. "macro1 02/02/04", "macro2 02/02/04". I have written a master macro chaining these macros together. The hassle is when the master macro is run, I'd be prompted by the various message boxes which belong to the individual macros. Is there anyway I can put some code in the master macro so that when the message boxes come up, they'd all get the same date? Obviously I don't want to change the individual macros because they are still needed as standalone macros from time to time. TIA. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Single Message Box for chained macros
John,
Thanks for your help. As you suggested, I solve the problem by declaring a public variable and asking for the date in the master macro module and adding IF statements to each inputbox. Everything is now running smoothly. "John Wilson" wrote in message ... Shatin, You could use the ideas that I gave you before or play around with something like this: Public MyVal As String Sub TestMe() MyVal = "12/22/04" If IsDate(MyVal) Range("A1") = InputBox(prompt:="Message", Title:="Title", Default:=MyVal) Else ' your original InputBox (without the date being populated) End If End Sub With the above, set MyVal from each of the InputBoxes. If there's a date in that variable, it'll use it and if not, it'll have you fill it in. As an afterthought, if MyVal is nothing, you don't even need the IF statement Public MyVal as String Sub TestMe2() Range("A1") = InputBox(prompt:="Message", Title:="Title", Default:=MyVal) End Sub You might want to reset MyVal at some point as it'll be there as long as the workbook is opened (maybe at the very end of the last macro)?? John "Shatin" wrote in message ... Oops, you are right. I mean Input Box. "John Wilson" wrote in message ... Shatin, Is it a message box?? I kinda doubt it because you can't input data to a message box. Is it an InputBox or a UserForm?? Without seeing the code, it's difficult to answer your question. What you could do is set a public variable in a regular module and populate it with the date from any of your ??boxes. Then you could check for that date. For an InputBox, just use an IF statement before it. If IsDate(MyDate) = false Then ' show my input box End If Or if it's a text box on a UserForm...... In the Activate Event. If IsDate(MyDate) Then TextBox1 = MyDate End If John "Shatin" wrote in message ... I have several macros which are all to do with file generations. They all begin with a message box asking for a date. The resulting filenames depend on the date entered, e.g. "macro1 02/02/04", "macro2 02/02/04". I have written a master macro chaining these macros together. The hassle is when the master macro is run, I'd be prompted by the various message boxes which belong to the individual macros. Is there anyway I can put some code in the master macro so that when the message boxes come up, they'd all get the same date? Obviously I don't want to change the individual macros because they are still needed as standalone macros from time to time. TIA. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Single Message Box for chained macros
Hi
Your individual macros would have to know they are being called by the master macro, which would mean the master macro has to pass a parameter value to them (say the date you require). That, in turn, would mean that the individual macros would need to read the parameter value. So your individual macros will require some code too. One way might be to add an optional parameter to your individual macros Sub Macro1(Optional DateString as String) You would now have a few lines of code in the macro like this If not IsMissing(DateString) then 'use DateString in your Message Box Else 'go with original Macro1 code End If If this optional parameter is left out, Macro1 runs as normal. Hard to be any more specific without seeing some code. regards Paul "Shatin" wrote in message ... I have several macros which are all to do with file generations. They all begin with a message box asking for a date. The resulting filenames depend on the date entered, e.g. "macro1 02/02/04", "macro2 02/02/04". I have written a master macro chaining these macros together. The hassle is when the master macro is run, I'd be prompted by the various message boxes which belong to the individual macros. Is there anyway I can put some code in the master macro so that when the message boxes come up, they'd all get the same date? Obviously I don't want to change the individual macros because they are still needed as standalone macros from time to time. TIA. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
One Single Message Box for chained macros
Shatin,
Thanks for the feedback. Happy to have been a help. John "Shatin" wrote in message ... John, Thanks for your help. As you suggested, I solve the problem by declaring a public variable and asking for the date in the master macro module and adding IF statements to each inputbox. Everything is now running smoothly. "John Wilson" wrote in message ... Shatin, You could use the ideas that I gave you before or play around with something like this: Public MyVal As String Sub TestMe() MyVal = "12/22/04" If IsDate(MyVal) Range("A1") = InputBox(prompt:="Message", Title:="Title", Default:=MyVal) Else ' your original InputBox (without the date being populated) End If End Sub With the above, set MyVal from each of the InputBoxes. If there's a date in that variable, it'll use it and if not, it'll have you fill it in. As an afterthought, if MyVal is nothing, you don't even need the IF statement Public MyVal as String Sub TestMe2() Range("A1") = InputBox(prompt:="Message", Title:="Title", Default:=MyVal) End Sub You might want to reset MyVal at some point as it'll be there as long as the workbook is opened (maybe at the very end of the last macro)?? John "Shatin" wrote in message ... Oops, you are right. I mean Input Box. "John Wilson" wrote in message ... Shatin, Is it a message box?? I kinda doubt it because you can't input data to a message box. Is it an InputBox or a UserForm?? Without seeing the code, it's difficult to answer your question. What you could do is set a public variable in a regular module and populate it with the date from any of your ??boxes. Then you could check for that date. For an InputBox, just use an IF statement before it. If IsDate(MyDate) = false Then ' show my input box End If Or if it's a text box on a UserForm...... In the Activate Event. If IsDate(MyDate) Then TextBox1 = MyDate End If John "Shatin" wrote in message ... I have several macros which are all to do with file generations. They all begin with a message box asking for a date. The resulting filenames depend on the date entered, e.g. "macro1 02/02/04", "macro2 02/02/04". I have written a master macro chaining these macros together. The hassle is when the master macro is run, I'd be prompted by the various message boxes which belong to the individual macros. Is there anyway I can put some code in the master macro so that when the message boxes come up, they'd all get the same date? Obviously I don't want to change the individual macros because they are still needed as standalone macros from time to time. TIA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Document contains macros message | Excel Discussion (Misc queries) | |||
Message Pop up Macros | Excel Discussion (Misc queries) | |||
Single message box for multiple IF statements | Excel Programming | |||
Can I "sign" my own macros? or some other fix for the message about security and macros? | Excel Programming | |||
Pop up message control using macros | Excel Programming |