PrintPreview Zoom

[CG Rosén]

Hi Group,

Using the code below to show the PrintPreview page
of a worksheet. Works fine, but is there a way to get
the PrintPreview page to open in the zoomed position?

Private Sub CommandButton3_Click()
UserForm1.Hide
ActiveSheet.PrintOut Preview:=True
UserForm1.Show
End Sub

Many thanks for some hints!

Brgds
CG Rosén

 

[Dave Peterson]

I failed when I added a Sendkeys "{enter}" to your code:

Private Sub CommandButton3_Click()
UserForm1.Hide
Sendkeys "{enter}"
ActiveSheet.PrintOut Preview:=True
UserForm1.Show
End Sub

But even worse, when I was testing, I noticed that excel liked to help. If I
was zooming and closed print preview, the next time I did it, I was in zoom
mode.

If I wasn't zoomed, then the next time I did it, I wasn't in zoom mode.

I'm not sure if there's a way to find out what mode you're in (and then do
anything with it).

 

[okaizawa]

Hi,

I think that there are little method to handle a preview window in excel
macro. I wrote some code using timer and sendkeys in Excel 2000. (this
may not be a good design)

Private Declare Function SetTimer Lib "user32" ( _
ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, ByVal uIDEvent As Long) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, ByVal lpszWindow As String) As Long
Private Declare Function GetWindow Lib "user32" ( _
ByVal hwnd As Long, ByVal uCmd As Long) As Long
Private Declare Function IsWindowVisible Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function IsWindowEnabled Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function LockWindowUpdate Lib "user32" ( _
ByVal hwndLock As Long) As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Const GW_CHILD = 5

Private g_zoom As Integer
Private g_keys_1 As String

Public Function TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, _
ByVal idEvent As Long, ByVal dwTime As Long) As Long
Dim xl As Long, prev As Long, i As Long
Dim scr1 As Long, scr2 As Long, scr3 As Long
On Error Resume Next
KillTimer 0, idEvent
For i = 1 To 10
xl = FindWindowEx(0, 0, "XLMAIN", Application.Caption)
prev = GetWindow(xl, GW_CHILD)
scr1 = FindWindowEx(prev, 0, "ScrollBar", vbNullString)
scr2 = FindWindowEx(prev, scr1, "ScrollBar", vbNullString)
scr3 = FindWindowEx(prev, scr2, "ScrollBar", vbNullString)
If scr1 <> 0 And scr2 <> 0 And scr3 <> 0 Then
If IsWindowEnabled(scr3) = 0 Then
If IsWindowVisible(scr2) = g_zoom Then
SendKeys "z"
End If
If g_keys_1 <> "" Then SendKeys g_keys_1
SetTimer 0, 0, 100, AddressOf TimerProc2
Exit Function
End If
End If
Sleep 100
Next
LockWindowUpdate 0
End Function

Public Function TimerProc2(ByVal hwnd As Long, ByVal uMsg As Long, _
ByVal idEvent As Long, ByVal dwTime As Long) As Long
On Error Resume Next
KillTimer 0, idEvent
LockWindowUpdate 0
End Function

Public Sub PreviewZoom(Zoom As Boolean, Optional SendKeysStr As String)
If Zoom Then g_zoom = 0 Else g_zoom = 1
g_keys_1 = SendKeysStr
LockWindowUpdate FindWindowEx(0, 0, "XLMAIN", Application.Caption)
SetTimer 0, 0, 0, AddressOf TimerProc
End Sub


Sub Test_PreviewZoom()
PreviewZoom False
ActiveSheet.PrintPreview
End Sub

Sub Test_PreviewZoom_2()
PreviewZoom True, "^{home}{down 6}{right 12}"
ActiveSheet.PrintPreview
End Sub

 

[Dave Peterson]

I'm not sure I'd depend on sendkeys, but your code worked ok for me in my simple
tests in xl2003, too.