PrintPreview Zoom

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

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).



"CG Rosén" wrote:
>
> 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

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

--
HTH,

okaizawa


Dave Peterson wrote:
> 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).
>
>
>
> "CG Rosén" wrote:
>
>>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
>
>

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



okaizawa wrote:
>
> 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
>
> --
> HTH,
>
> okaizawa
>
> Dave Peterson wrote:
> > 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).
> >
> >
> >
> > "CG Rosén" wrote:
> >
> >>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