Excel - VBA to close a Print Preview window

  Jas14 20:16 28 Jan 04
Locked

I have disabled the Menu bar in favour of a cutomised Menu that includes a Print Preview option.

I have placed a command button (CB)on my toolbar that should do the same job as the Close option in the standard print preview toolbar.

I currently press Escape to close the print preview but I would like the CB to do the close as well as the Escape key.

What code would I need to add to the click event of the CB to send an Escape signal?

Many thanks in anticipation

  VoG II 20:36 28 Jan 04

SendKeys {ESC}

  VoG II 20:39 28 Jan 04

SendKeys "{ESC}"

  Jas14 21:19 28 Jan 04

Thank you for your response.

I am confident that it will work but only when I can get the Print Preview screen to release the focus in favour of the CB on the userform (ShowModal is set to False)

Any further assistance would be gratefully received.

  VoG II 22:03 28 Jan 04

Try

SendKeys "{ESC}"

DoEvents

  Jas14 22:23 28 Jan 04

I tried as suggested with no luck, I can not get the focus away from the print preview, so I might have to use the MsgBox to inform the user how to get out of it.

I have shown the code below where PA1 and PA2 are string variables and Preview and ClosePreview are command buttons on the nonmodal userform acting as a toolbar.

==============================

Private Sub ClosePreview_Click()

SendKeys "{Esc}"

DoEvents

End Sub

==============================

Private Sub Preview_Click()

'MsgBox ("Press Esc Key to exit preview")Trying to do away with this!

ActiveSheet.PageSetup.PrintArea = PA1

Preview.Visible = False

ClosePreview.Visible = True

ActiveWindow.SelectedSheets.PrintPreview

ClosePreview.SetFocus

ActiveSheet.PageSetup.PrintArea = PA2

ActiveWindow.SelectedSheets.PrintPreview

Preview.Visible = True

ClosePreview.Visible = False

ActiveSheet.PageSetup.PrintArea = PA1

End Sub

==============================

  VoG II 22:38 28 Jan 04

Hmm. I can't for the moment think of a way around this. As an alternative to using a MsgBox you could use the Status Bar:

Application.StatusBar = "Press Esc Key to exit preview"

If you do this, don't forget to turn it off using

Application.StatusBar = False

The advantage of this is that the user wouldn't have to click an OK button. The disadvantage is that they might not notice the message!

  Jas14 09:12 29 Jan 04

Once again thank you for your efforts. I have ticked the subject as resolved because, like you, I can not find a way around the problem.

Your suggestion about the status bar is well taken and I will use it in preference to the Msgbox.

I will also try another modeless form to draw attention to the required action that closes on the Esc key event or on a timed sequence to see if that works.

Again many thanks

Jas

  Jas14 18:39 29 Jan 04

Just to tidy this thread up, I regret to report that the Applcation.StatusBar option does not work because of the iron grip by the Preview screen.

I still hope that someone can expose the achilles heel of the Preview pane, even if it is only to allow VBA access to the Close sequence.

I have adopted another modeless form that displays the message to use the escape key to exit the preview mode, it is called by the call to Preview the printing and closed at the end of the routine.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…