Closing Print Preview Screen with Visual Basic Code 1

[olympus]

I would like to either: 1) Close the print preview screen from a VB code module or 2) Print the worksheet from the print preview screen using VB. I have tried the suggestions given to be17 (thread 707-104728) without any success. I have also tried using the sendkeys command to send a "c" or {escape} to close the print preview window. I also tried using the sendkkeys command to send the letter "p" to print from the print preview screen.

The reason I want to do this is that the named range that I have for the print area changes based on the item selected. If I use the printout command I get the print area from the PREVIOUS item. However, if I open the worksheet in PrintPreview it shows the correct print range. Any help on this would be appreciated. The current code attached to a print "button" is:

Private Sub CommandButton1_Click()

CommandBars("worksheet menu bar").Enabled = True
CommandBars("standard").Enabled = True
Worksheets("Sheet2").Activate
Worksheets(2).ResetAllPageBreaks
Me.PageSetup.PrintArea _
= Me.Parent.Names(Me.Range("B3").Value).RefersToRange.Address
ActiveWindow.SelectedSheets.PrintOut

Application.SendKeys ("%fx")
Application.SendKeys ("n")
End Sub


Thanks!
John

 

[BrainB]

I do not think this is possible. The print preview screen does not return control back to the macro until it is closed.

Having had experience of running external applications with SendKeys and API calls I find this most unusual.




Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[IvanMoala]

You can do this via API calls and sendkeys
BUT you need to be using XL2000+ due to Addressof operator

Have a look here

http://www.xcelfiles.com/API_02.html

Option Explicit
Declare Function SetTimer Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerfunc As Long) As Long

Declare Function KillTimer Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long) As Long

'==========Public Declarations ==============================
Public TimerID As Long            'Turn on and off with this ID
Public TimerActive As Boolean     'Is the timer active
Public Const tmMin As Long = 2    'Minimum time allowed
Public Const tmDef As Long = 5    'Default if min set to tmMin
'============================================================

Public Sub ActivateMyTimer(ByVal sec As Long)
sec = sec * 1000
If TimerActive Then Call DeActivateMyTimer

On Error Resume Next
TimerID = SetTimer(0, 0, sec, AddressOf Timer_CallBackFunction)
TimerActive = True

End Sub

Public Sub DeActivateMyTimer()
KillTimer 0, TimerID
End Sub

Function Timer_CallBackFunction( _
    ByVal hwnd As Long, _
    ByVal uMsg As Long, _
    ByVal idevent As Long, _
    ByVal Systime As Long) As Long

SendKeys "%C", True
If TimerActive Then Call DeActivateMyTimer

End Function


Sub PrintPreview()
'// Set timer for 5 secs change as required
'// But min is set @ 5
    ActivateMyTimer 5
    ActiveWindow.SelectedSheets.PrintPreview
    MsgBox "The rest of my code is running"
End Sub

Ivan F Moala

http://www.xcelfiles.com