Excel - VBA to close a Print Preview window

  Jas14 20:16 28 Jan 2004
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 2004

SendKeys {ESC}

  VoG II 20:39 28 Jan 2004

SendKeys "{ESC}"

  Jas14 21:19 28 Jan 2004

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 2004

Try

SendKeys "{ESC}"

DoEvents

  Jas14 22:23 28 Jan 2004

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 2004

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 2004

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 2004

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.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?