Pausing a Macro awaiting user input

[jayklmno]

I am working on a macro that will place an object at the cursor position on
the excel sheet. I need to find a way to pause te loop until a click or
keystroke and then continue with the macro. I have dabbled with onkey and
sendkeys but can't seem to getthe macro to stop until the user place sthe
curor and is ready to move on. Any help?

 

[Jim Thomlinson]

You can not really pause or suspend a macro. What you can do is to execute
everything up to where you require the users input as one macro and then
using the Sheet Change event initiate another batch of code when the user has
completed their input... so you need 2 macros. One before and one after the
user input.

 

[Gord Dibben]

'some code then when ready for user to select a cell

Set mycell = Application.InputBox( _
Prompt:="Select the Destination Cell", _
Title:="Pick a cell", Type:=8)

'more code


Gord Dibben MS Excel MVP

 

[jayklmno]

Maybe I didn't explain it well enough... here's what I'm trying to do... I
have a two tabbed Excel spreadsheet, on one tab is a map of the US. On the
other tab I have sales information by state. In another cell on the same tab,
I have a round circle that displays three different colors if the sales in
that state are below/on average/or above sales plan.

When the macro starts I want to prompt the User to select the first state on
the map with the cursor, at the location of the cursor I want a text box to
be drawn showing the sales data from the other tab and the move on to the
next state.

The states is just an example, it's more complicated than that, but you get
he idea.

Any suggestions what to use to to know when the user is ready to draw the
text box?

 

[NickHK]

How are you determining which state from the map was clicked ?

NickHK

 

[jayklmno]

The location of the cursor on the screen.

How are you determining which state from the map was clicked ?

NickHK

 

[NickHK]

Assign a macro to the picture that runs the processing code.

NickHK