Pausing a Macro awaiting user input
 

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?

Pausing a Macro awaiting user input
 

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

Jim Thomlinson


"jayklmno" wrote:

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?

Pausing a Macro awaiting user input
 

'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

On Thu, 28 Jun 2007 11:36:01 -0700, jayklmno
wrote:

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?

Pausing a Macro awaiting user input
 

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?

"jayklmno" wrote:

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?

Pausing a Macro awaiting user input
 

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

NickHK

"jayklmno" wrote in message
...
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?

"jayklmno" wrote:

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?

Pausing a Macro awaiting user input
 

The location of the cursor on the screen.

"NickHK" wrote:

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

NickHK

"jayklmno" wrote in message
...
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?

"jayklmno" wrote:

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?

Pausing a Macro awaiting user input
 

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

NickHK

"jayklmno" wrote in message
...
The location of the cursor on the screen.

"NickHK" wrote:

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

NickHK

"jayklmno" wrote in message
...
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?

"jayklmno" wrote:

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?