Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
Back Lotus 1-2-3 we could have a Macro pause with {?}. How do you pause an
Excel macro for user input? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
You don't pause a macro for input. You end the first macro when you require
input from the user. Then based on Change Events which get fired when the user changes values in cells you can initiate the next set of macros as required. while that may not seem as simple as the old Lotus method it is a lot more powerful in what you can do. Post back if you want some help with this... -- HTH... Jim Thomlinson "David Steed" wrote: Back Lotus 1-2-3 we could have a Macro pause with {?}. How do you pause an Excel macro for user input? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
hi
look up the input box function in vb help, example of how it might be used... dim dat as string ' string if text, long if number dat = inputbox("input somthing") 'now you have a variable named dat and can do a lot with it..say... Range("A1").value = dat the input box is modal which means that all code stops and waits for user input. a modal form(any) also suspends toolbar icons, menu items and all other forms of input until the user clicks the ok button. in the case of the input box, there is a text box of user input. Regards FSt1 "David Steed" wrote: Back Lotus 1-2-3 we could have a Macro pause with {?}. How do you pause an Excel macro for user input? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
Can you show me a simple VBA code example of this?
David "Jim Thomlinson" wrote in message ... You don't pause a macro for input. You end the first macro when you require input from the user. Then based on Change Events which get fired when the user changes values in cells you can initiate the next set of macros as required. while that may not seem as simple as the old Lotus method it is a lot more powerful in what you can do. Post back if you want some help with this... -- HTH... Jim Thomlinson "David Steed" wrote: Back Lotus 1-2-3 we could have a Macro pause with {?}. How do you pause an Excel macro for user input? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
Jim
Why would this method be better than a simple InputBox pause for user to enter something then continue? Gord Dibben MS Excel MVP On Thu, 13 Dec 2007 07:46:00 -0800, Jim Thomlinson wrote: You don't pause a macro for input. You end the first macro when you require input from the user. Then based on Change Events which get fired when the user changes values in cells you can initiate the next set of macros as required. while that may not seem as simple as the old Lotus method it is a lot more powerful in what you can do. Post back if you want some help with this... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
The simplest version would be that you need the user to fill in something to
just one cell before continuing. In that case you could use a macro such as this... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MsgBox "Call your macro..." End Sub Paste this code directly into the sheet by right clicking the sheet tab and selecting View Code. Now when cell A1 is changed this macro will fire... You can get a lot fancier than this requiring a number of cell to be completed in a satisfactory manner or ??? -- HTH... Jim Thomlinson "David Steed" wrote: Can you show me a simple VBA code example of this? David "Jim Thomlinson" wrote in message ... You don't pause a macro for input. You end the first macro when you require input from the user. Then based on Change Events which get fired when the user changes values in cells you can initiate the next set of macros as required. while that may not seem as simple as the old Lotus method it is a lot more powerful in what you can do. Post back if you want some help with this... -- HTH... Jim Thomlinson "David Steed" wrote: Back Lotus 1-2-3 we could have a Macro pause with {?}. How do you pause an Excel macro for user input? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
Depends what you are trying to do. If it is as simple as a single value that
is needed then an input box can work great. If however you need the user to do something a bit more complicated such as review the results generated by the macro or to add a bunch of stuff to multiple cells then events programming is my preference. Sometimes I reply in absolute terms when the best answer might be a bit of a grey area. Thanks for pointing that out. -- HTH... Jim Thomlinson "Gord Dibben" wrote: Jim Why would this method be better than a simple InputBox pause for user to enter something then continue? Gord Dibben MS Excel MVP On Thu, 13 Dec 2007 07:46:00 -0800, Jim Thomlinson wrote: You don't pause a macro for input. You end the first macro when you require input from the user. Then based on Change Events which get fired when the user changes values in cells you can initiate the next set of macros as required. while that may not seem as simple as the old Lotus method it is a lot more powerful in what you can do. Post back if you want some help with this... |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
Thanks Jim
Valid point about reviewing results. I got the message that OP wanted to pause for user entry as stated in original post. I stopped processing after that<g Gord On Thu, 13 Dec 2007 09:06:00 -0800, Jim Thomlinson wrote: Depends what you are trying to do. If it is as simple as a single value that is needed then an input box can work great. If however you need the user to do something a bit more complicated such as review the results generated by the macro or to add a bunch of stuff to multiple cells then events programming is my preference. Sometimes I reply in absolute terms when the best answer might be a bit of a grey area. Thanks for pointing that out. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exel Stop Macro
My problem is that I kept processing well after the op had finished writing
<g. The complexity of my answer often depends on the project I am working on at the time. My last project required the user to fill out multiple cells each with their own validation. There was no way in heck I was going to use an input box or user form for that one... If I had been working on something simpler my answer would probably have been different. If you ever find yourself on this side of the pond I will buy you a beer and we can discuss the finer points of input boxes... -- HTH... Jim Thomlinson "Gord Dibben" wrote: Thanks Jim Valid point about reviewing results. I got the message that OP wanted to pause for user entry as stated in original post. I stopped processing after that<g Gord On Thu, 13 Dec 2007 09:06:00 -0800, Jim Thomlinson wrote: Depends what you are trying to do. If it is as simple as a single value that is needed then an input box can work great. If however you need the user to do something a bit more complicated such as review the results generated by the macro or to add a bunch of stuff to multiple cells then events programming is my preference. Sometimes I reply in absolute terms when the best answer might be a bit of a grey area. Thanks for pointing that out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a Macro on exel be set with a password? | Excel Worksheet Functions | |||
Stop 150.00 in Exel Beta showing as 1.50. | Excel Discussion (Misc queries) | |||
stop exel from rounding | New Users to Excel | |||
HOw DO I EMAIL AN EXEL MACRO TO ANTOEHR USER? | Excel Discussion (Misc queries) | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) |