Pause/Wait for a keystroke

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,723
Office Version
  1. 2010
Platform
  1. Windows
First edit your profile account to fill the Office version and the OS Platform …​
 

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,723
Office Version
  1. 2010
Platform
  1. Windows
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) :​
VBA Code:
Private Declare PtrSafe Function GetKeyState% Lib "User32.dll" (ByVal nVirtKey&)

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

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199

ADVERTISEMENT

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) :​
VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
Perhaps you can put an OK-Cancel message box before each printing, like this:
VBA Code:
For i = B To E
If MsgBox("Continue?", vbOKCancel) = vbCancel Then Exit Sub
ActiveWindow.SelectedSheets.PrintOut Copies:=1
 
Solution

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199
Perhaps you can put an OK-Cancel message box before each printing, like this:
VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Forum statistics

Threads
1,147,517
Messages
5,741,629
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top