entering cell values while a macro is running

N

NeonRedSharpie

Well-known Member

Joined

Jul 14, 2014

Messages

1,678

• Aug 15, 2014

• #2

Code:

Application.Wait(Now + #0:00:10#)

Nevermind...ignore me...that doesn't give you control.

 

Last edited: Aug 15, 2014

Upvote 0

A

Arithos

Well-known Member

Joined

Aug 14, 2014

Messages

598

• Aug 15, 2014

• #3

Is it random when you would want to stop it? Or is it always in a specific place in the code?

If the code is not that long you could manually run the code using F8 and its dynamic where you stop.

If its a specific place you can put a

Code:

Stop

(have VBA open for this)
In the code where you would like to change something, and do the change, and press "F5" again..

 

Last edited: Aug 15, 2014

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Aug 15, 2014

• #4

thanks - there is a definite place in the code where I want to check if say cell A1 is still empty, but to get a value into A1 I need to "stop" the macro - can a macro be made to pass or offer control to the keyboard ?

 

Upvote 0

N

NeonRedSharpie

Well-known Member

Joined

Jul 14, 2014

Messages

1,678

• Aug 15, 2014

• #5

Ok, so you don't want to stop the macro, you just want to populate A1.

Code:

If IsEmpty(Cells(1, 1)) Then    
     Cells(1, 1).value = InputBox("A1 is not populated. Please populate it.")
Else
    'do nothing
End If

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Aug 15, 2014

• #6

many thanks, the macro will run for maybe 5 minutes before I want to put a value into A1 - maybe I am not explaining the situation clearly enough. As soon as there is a value in A1 the macro does a number of things before terminating. It is how I can get a value into A1 while the macro is running ?

 

Upvote 0

N

NeonRedSharpie

Well-known Member

Joined

Jul 14, 2014

Messages

1,678

• Aug 15, 2014

• #7

So at the point of where you want to break the code, put that code in there. This will prompt for user input. It's the same as passing control to the user.

 

Upvote 0

A

Arithos

Well-known Member

Joined

Aug 14, 2014

Messages

598

• Aug 15, 2014

• #8

NeonRedSharpie said:

So at the point of where you want to break the code, put that code in there. This will prompt for user input. It's the same as passing control to the user.

Click to expand...

He is correct. As long as the only value you would like to change is "A1" =)

What code runs for five minutes tho, I think that can be sped up, you have dimmed it, and hid application updates etc?

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Aug 16, 2014

• #9

the code generates bingo numbers for my 2 grandchildren and needs to give them time to tick off the numbers - so when one calls bingo I want to populate A1 which will divert the macro to checking routine - I am using excel 2000 and have managed to use speech to call out the numbers.....

 

Upvote 0

FormR

MrExcel MVP

Joined

Aug 18, 2011

Messages

7,148

Office Version

1. 365

Platform

1. Windows

• Aug 16, 2014

• #10

oldbrewer said:

thanks - there is a definite place in the code where I want to check if say cell A1 is still empty, but to get a value into A1 I need to "stop" the macro - can a macro be made to pass or offer control to the keyboard ?

Click to expand...

Try this at the point you want to pause the macro:

Control will be handed back to the application, the code will continue to run once A1 is populated.

Code:

Do Until Range("A1").Value <> ""
DoEvents
Loop

 

Upvote 0