Pause/Wait for a keystroke

[StanSz]

I have a very short 10 line VBA printing routine that I'm looking for a way to add a 'pause/wait for a keystroke' code.

My situation is as follows: I have a batch of envelopes, now and in the future, that I need to print. These envelopes need to be fed manually, one by one, into the printer.

The data to be printed is on a worksheet with the row # of the desired data specified by integer of the row # in Cell C3. Changing the value in C3 will change the data displayed and printed. The end data's row is specified by Cell C4.

Thus if C3 = 26 and C4 = 37, the loop will display (and print) the envelopes with the data between Rows 26 and 37.

After an envelope is printed the loop is incremented by one and if the the value of the counter is less than the Ending row, then the value of C3 is changed, and the printing continues.

This routine works great when printing normal sheets of paper. However, since the envelopes need to be fed manually into the printer, I want to have a 'Pause/Wait for a keystroke' line of code after each printing. I don't want to use a Time delay, since I don't know how long it will take to align the next envelope, nor if I will get a phone call in the middle of the printing process.

Back in the 1980-1990's when I was doing Applesoft Basic programming this Pause/Wait problem could be solved by a simple 'Get A$' command which would wait for an input (any input) before continuing. I don't see any VBA instruction similar to the 'Get' and so I'm looking for a way to accomplish the same thing.

Thanks in advance.
StanSz

My code is attached:

Dim i As Integer 'Counter
Dim B As Integer 'Beg row #
Dim E As Integer ' End row #

B = Range("C3").Value
E = Range("C4").Value

For i = B To E

ActiveWindow.SelectedSheets.PrintOut Copies:=1

'GET routine to wait for spacebar

If i < E Then Range("C3").Value = i + 1 ' Increment Cell if less than end

Next i

 

[Marc L]

​

First edit your profile account to fill the Office version and the OS Platform …​

 

[StanSz]

​

First edit your profile account to fill the Office version and the OS Platform …​

Marc, Thanks for the reply. Under my Profile I do not see any reference to Office Version nor OS Platform ?? But, I'm working on a Windows 10 desktop with Excel 2002.

 

[Marc L]

An Excel VBA for Windows demonstration to paste to the top of a VBA module (for Excel versions prior to 2010 version remove PtrSafe statement) :​

Private Declare PtrSafe Function GetKeyState% Lib "User32.dll" (ByVal nVirtKey&)

Sub DemoWait4Space()
    Do:  DoEvents:  Loop Until GetKeyState(32) < 0
    Beep
End Sub

 

[StanSz]

An Excel VBA for Windows demonstration to paste to the top of a VBA module (for Excel versions prior to 2010 version remove PtrSafe statement) :​

Private Declare PtrSafe Function GetKeyState% Lib "User32.dll" (ByVal nVirtKey&)

Sub DemoWait4Space()
    Do:  DoEvents:  Loop Until GetKeyState(32) < 0
    Beep
End Sub

Marc,
Thanks for the code on using GetKeyState. Not knowing of that routine I had to search and read a whole lot.

At the end of my research I think that your code is not doing what I wanted, which was to Wait after each loop iteration to allow time for inserting the next envelope. In fact the code seems to be doing the opposite, that is, it keeps running the loop UNTIL I press the spacebar -- which of course would be after the entire loop was run.

But, my research led to the following simple solution: add this one line of code to where I want the wait routine.

Msgbox "Click to go on", , "Example"

This code will stop the loop routine after the first printed envelope and wait for my response. Either the SpaceBar or the Enter key will suffice to trigger the next loop iteration.

Thanks for your help and guidance which directed me to be able to answer my own question -- the best way to learn.

Stan

 

[Akuini]

Perhaps you can put an OK-Cancel message box before each printing, like this:

For i = B To E
If MsgBox("Continue?", vbOKCancel) = vbCancel Then Exit Sub
ActiveWindow.SelectedSheets.PrintOut Copies:=1

 

[StanSz]

Perhaps you can put an OK-Cancel message box before each printing, like this:

For i = B To E
If MsgBox("Continue?", vbOKCancel) = vbCancel Then Exit Sub
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Dear Akuini,

Thanks -- that's a great idea. Who know's what can happen. I've already made the change.
Stan

 

[Akuini]

You're welcome.