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