Run macro but let user update spreadsheet while it runs

How can I run an Excel macro but let the user update the spreadsheet while
the macro is running?

You can't. Give us a little more info and we can probably help you to
accomplish what you want though...
--
HTH...

Jim Thomlinson


"Ev" wrote:

> How can I run an Excel macro but let the user update the spreadsheet while
> the macro is running?

I'm writing a navigation simulator. The macro contantly updates the
simulated postion but needs to allow the user to update cells that contain
data such as heading and speed that the macro uses. I'd rather not require
the user to stop and restart the macro if possible since the calculations all
require elapsed time e.g. distance = rate * time.

"Jim Thomlinson" wrote:

> You can't. Give us a little more info and we can probably help you to
> accomplish what you want though...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Ev" wrote:
>
> > How can I run an Excel macro but let the user update the spreadsheet while
> > the macro is running?

On Fri, 9 Sep 2005 10:41:01 -0700, "Ev" <[email protected]>
wrote:

Could you record the times the macro starts and stops and adjust your
elapsed time cells accordingly to take account of this?

Rgds





>I'm writing a navigation simulator. The macro contantly updates the
>simulated postion but needs to allow the user to update cells that contain
>data such as heading and speed that the macro uses. I'd rather not require
>the user to stop and restart the macro if possible since the calculations all
>require elapsed time e.g. distance = rate * time.
>
>"Jim Thomlinson" wrote:
>
>> You can't. Give us a little more info and we can probably help you to
>> accomplish what you want though...
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "Ev" wrote:
>>
>> > How can I run an Excel macro but let the user update the spreadsheet while
>> > the macro is running?

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Yes. But I'd like to avoid stopping and restarting the macro.

In Pascal I believe there was a "keypressed" command (boolean). Using this
command you could check to see if a key was pressed by the user while the
program was running in a loop. If so, it allowed the program to go to the
buffer and get that key. Then based on what key was pressed, the program
could branch to code that performed the action the user wanted.

To put that into practical terms for my spreadsheet, I'd like the user to
simply press the "H" key if he wants to change heading or the "S" key to
change speed.

"Ev" wrote:

> How can I run an Excel macro but let the user update the spreadsheet while
> the macro is running?

There is an on chnge event that will fire each time a cell is changed. You
can capture this event and run your macro based on what was changes to what
in which cell

In the VB editor double click the sheet you want to add the code to (in the
project explorer). There is a drop down at the top left of the code window
indicating general. Change it to Worksheet. Doing this will add a selection
change sub to your procedure which you can just delete. In the drop down to
the right of that you can select which event you want. Choose Change. You
will get code similar to this

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address & vbTab & Target.Value
End Sub

I have added a message box showing what was changed and where. There is no
keypress event on a sheet.
--
HTH...

Jim Thomlinson


"Ev" wrote:

> Yes. But I'd like to avoid stopping and restarting the macro.
>
> In Pascal I believe there was a "keypressed" command (boolean). Using this
> command you could check to see if a key was pressed by the user while the
> program was running in a loop. If so, it allowed the program to go to the
> buffer and get that key. Then based on what key was pressed, the program
> could branch to code that performed the action the user wanted.
>
> To put that into practical terms for my spreadsheet, I'd like the user to
> simply press the "H" key if he wants to change heading or the "S" key to
> change speed.
>
> "Ev" wrote:
>
> > How can I run an Excel macro but let the user update the spreadsheet while
> > the macro is running?