[SOLVED] LockWindowUpdate

[Michael Malinsky]

I have tried the methods posted in this NG to freeze the screen when I
update print setting via VBA. I have the following code included as an

add-in:
-----------------
Option Explicit


Private Declare Function LockWindowUpdate Lib "USER32" (ByVal hwndLock
As Long) As Long
Private Declare Function GetDesktopWindow Lib "USER32" () As Long
-----------------
Private Sub Print_Draft()


Dim x As Integer
Dim sh As Object
Dim mycontrol As Object


WindowUpdating (False) 'to freeze the screen


Set mycontrol =3D CommandBars("Worksheet Menu Bar").Controls("Time
Savers")
If mycontrol.Controls("Print Draft").State =3D msoButtonUp Then
'To change printer settings to print draft watermark.
Application.SendKeys ("^p")
For x =3D 1 To 6
Application.SendKeys ("{TAB}")
Next x
Application.SendKeys ("~")
For x =3D 1 To 2
Application.SendKeys ("{LEFT}")
Next x
For x =3D 1 To 3
Application.SendKeys ("{TAB}")
Next x
Application.SendKeys ("~")
For x =3D 1 To 21
Application.SendKeys ("{DOWN}")
Next x
Application.SendKeys ("{RIGHT}")
For x =3D 1 To 3
Application.SendKeys ("{DOWN}")
Next x
Application.SendKeys ("~")
Application.SendKeys ("{LEFT}")
Application.SendKeys ("{TAB}")
Application.SendKeys ("~")
Application.SendKeys ("{TAB}")
Application.SendKeys ("~")
For x =3D 1 To 11
Application.SendKeys ("{TAB}")
Next x
Application.SendKeys ("~")
mycontrol.Controls("Print Draft").State =3D msoButtonDown
'To change printer settings to remove print draft watermark.
ElseIf mycontrol.Controls("Print Draft").State =3D msoButtonDown Then

Application.SendKeys ("^p")
For x =3D 1 To 6
Application.SendKeys ("{TAB}")
Next x
Application.SendKeys ("~")
For x =3D 1 To 2
Application.SendKeys ("{LEFT}")
Next x
For x =3D 1 To 3
Application.SendKeys ("{TAB}")
Next x
Application.SendKeys ("~")
For x =3D 1 To 21
Application.SendKeys ("{DOWN}")
Next x
Application.SendKeys ("{RIGHT}")
For x =3D 1 To 3
Application.SendKeys ("{DOWN}")
Next x
For x =3D 1 To 2
Application.SendKeys ("{UP}")
Next x
Application.SendKeys ("~")
Application.SendKeys ("{LEFT}")
Application.SendKeys ("{TAB}")
Application.SendKeys ("~")
Application.SendKeys ("{TAB}")
Application.SendKeys ("~")
For x =3D 1 To 11
Application.SendKeys ("{TAB}")
Next x
Application.SendKeys ("~")
mycontrol.Controls("Print Draft").State =3D msoButtonUp
End If


WindowUpdating (True) 'to unfreeze the screen


End Sub
--------------------
Sub WindowUpdating(Enabled As Boolean)


'Completely Locks the Whole Application Screen Area, including dialogs
and the mouse.


Dim Res As Long


If Enabled Then
LockWindowUpdate 0 'Unlock screen area
Else
Res =3D LockWindowUpdate(GetDesktopWin=ADdow) 'Lock at desktop
level
End If


End Sub
---------------------


The code itself works fine, but I still see the various dialog boxes
popping up based on the SendKeys commands I used.


Am I missing something?=20


TIA,=20
Mike.

[Leith Ross]

Hello Mike,

The APi function LockWindowUpdate can only be applied to one window at time. Popups and dialogs are independent windows from the main application and so are not "Frozen". You can turn Excel's updating of the screen on and off which should eliminate the other windows. It would also be faster to recode you routine in VBA and directly control the printing process. Excel is complex and adding VBA into it makes it even more so. The API calls are an entirely separate system control langauge than should not be used unless you really understand them. Errors when using API calls can trash your system (read - can't reboot) because there are no builtin safegaurds.

To Turn Screen Updating On/Off

Application.ScreenUpdating = False (Turned Off)
Application.ScreenUpdating = True (Turned On)

This will help speed up the macro by no updating the screen everytime something is changed on the screen.

Hope this answers your questions,
Leith Ross