Macro with wait for enter key

[Jewguy]

How do I write a macro which goes to a cell then waits for the "enter key" to be pressed before continuing on with the macro?

 

[wob]

Would a message box do the trick?
That would stop the macro, and the 'Ok' button clicks when the enter key is pressed...

 

[Al Chara]

What about splitting the macro up. Have the first part run and then stop. Then have the rest of the macro in the click event of a command button.

 

[CYNSIE]

I am interested to know other ways to do this too, except I want to write a macro that stops at about 15 cells and waits for data input.

 

[Al Chara]

On 2002-05-23 13:53, CYNSIE wrote:
I am interested to know other ways to do this too, except I want to write a macro that stops at about 15 cells and waits for data input.

Once a macro is running then the user has no interaction except with the use of a msgbox or inputbox. You can use the Wait method, but that will suspend all Excel activity.

 

[stevebausch]

There are Worksheet.Change and Worksheet.SelectionChange events, those could be used.

I would think something along these lines:

Restrict the ScrollArea of the worksheet to only the cells you want entered and filled.
Have a counter set to zero.
Have the event (your choice) qualify the entries; this may be rather easy, or rather hard, I don't know what you are looking for.


You might need to loop through something like:

Restrict the selection area to a specific cell
Upon entry, analyze the response, and act accordingly
Restrict the selection area to the next specific cell
Upon entry, analyze the response, and act accordingly

You get the idea


I am currently playing with a sheet to determine how to do this.

So far....

Set Worksheet.EnableSelection = xlUnlockedCells

That will keep you from selecting locked cells.
This message was edited by stevebausch on 2002-05-23 15:57

 

[Nimrod]

In the worksheet that you want to "move and Pause" in drop this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Call Module1.GetDataAndWaitAtNextCell(Target.Address)
End Sub

Then have the macro that does the processing have a case statement that selects the particular cells

Public Sub GetDataAndWaitAtNextCell(Address)

Select Case Address

Case "$A1"
    Sheets(1).Range("$A3").Select
    Call ProcessStuff(Sheets(1).Range("$A1").Value)

Case "$A3"
    Sheets(1).Range("$A5").Select
    
Case "$A5"
    Sheets(1).Range("$A7").Select
End Select
End Sub

Does this seem to do the trick ?


Weird ... when I enter the code to this website the address gets changed ... it looses the $Number part of the address ... anyway I think you see what I'm doing
This message was edited by Nimrod on 2002-05-23 18:40
This message was edited by Nimrod on 2002-05-23 18:42

 

[Ivan F Moala]

Hi All here is another way to do it.
Useing Onkey to restart your Macro as Al
has suggested...ie running 2 parts.
Note All suggestions sound GOOD to me
this is just another way....

<pre/>
Sub TestPause()
Dim x As Integer
Dim y As Integer

For x = 1 To 200
For y = 1 To 10
Cells(x, y) = x * y
Next
Next

'// goto the cell you want
Application.Goto [A10], True
'// Set the Enter key up to run the next routine
Application.OnKey "{ENTER}", "Continue"

End Sub


Sub Continue()
'// Disable the Onkey now
Application.OnKey "{ENTER}"
MsgBox "The rest of my code is running!" & vbCr & _
"Try the Enter key NOW"

End Sub
</pre>