Exel Stop Macro

[David Steed]

Back Lotus 1-2-3 we could have a Macro pause with {?}. How do you pause an
Excel macro for user input?

 

[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...

 

[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

 

[David Steed]

Can you show me a simple VBA code example of this?

David

 

[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

 

[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 ???

 

[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.

 

[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

 

[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...