D
David Steed
Back Lotus 1-2-3 we could have a Macro pause with {?}. How do you pause an
Excel macro for user input?
Excel macro for user input?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jim Thomlinson
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...
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...
F
FSt1
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
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
D
David Steed
Can you show me a simple VBA code example of this?
David
David
G
Gord Dibben
Jim
Why would this method be better than a simple InputBox pause for user to enter
something then continue?
Gord Dibben MS Excel MVP
Why would this method be better than a simple InputBox pause for user to enter
something then continue?
Gord Dibben MS Excel MVP
J
Jim Thomlinson
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 ???
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 ???
J
Jim Thomlinson
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.
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.
G
Gord Dibben
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
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
J
Jim Thomlinson
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...
<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...