How to make API call to freeze excel window?

[Mikeyhend]

Hi,

Does anyone know how to freeze the active window using windows API?

I tried this:

Class named FreezeWindow:

Option Explicit

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal ClassName As String, ByVal WindowName As String) As Long

Private Declare Function LockWindowUpdate Lib "user32" _
(ByVal hWndLock As Long) As Long


Public Sub Freeze(Window As Window)

Dim hWnd As Long

hWnd = FindWindow("XLMAIN", Application.Caption)

End Sub

Public Sub Unfreeze()

LockWindowUpdate 0

End Sub

Private Sub Class_Terminate()

Unfreeze

End Sub

and in the macro:

dim FreezeWnd as New FreezeWindow

FreezeWnd.Freeze ActiveWindow

FreezeWnd.Unfreeze


I can't get it to work, don't know what's wrong (still learning VBA).

Thanks in advance
Mik

 

[Chip Pearson]

Your Freeze method does nothing of any value. All it does is find
the hWnd of XLMain, which you can get more simply with
Application.Hwnd.


Try the following to lock the window,

Dim Res As Long
Res = LockWindowUpdate(Application.Hwnd)

and the following to unlock the window

Dim Res As Long
Res = LockWindowUpdate(0&)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Mikeyhend"
message
news:[email protected]...

 

[Chip Pearson]

If you want to freeze only one Excel window, rather than the
Application window, use code like

Private Declare Function FindWindow Lib "user32" Alias
"FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String)
As Long
Private Declare Function LockWindowUpdate Lib "user32" _
(ByVal hWndLock As Long) As Long
Private Declare Function FindWindowEx Lib "user32" Alias
"FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As
String, _
ByVal lpsz2 As String) As Long

Sub Test()
FreezeWindow W:=ActiveWindow
End Sub


Sub FreezeWindow(W As Window)
Dim WindowHWnd As Long
Dim XLHwnd As Long
Dim DeskHwnd As Long
Dim Res As Long
XLHwnd = Application.HWnd
DeskHwnd = FindWindowEx(XLHwnd, 0&, "XLDESK", vbNullString)
WindowHWnd = FindWindowEx(DeskHwnd, 0&, "EXCEL7",
ActiveWindow.Caption)
Res = LockWindowUpdate(WindowHWnd)
End Sub

Sub UnFreezeWindow()
Dim Res As Long
Res = LockWindowUpdate(0&)
End Sub




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Mikeyhend"
message
news:[email protected]...

 

[Chip Pearson]

WindowHWnd = FindWindowEx(DeskHwnd, 0&, "EXCEL7",
ActiveWindow.Caption)

should be

WindowHWnd = FindWindowEx(DeskHwnd, 0&, "EXCEL7", W.Caption)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jon Peltier]

Why not use Application.ScreenUpdating?

- Jon

 

[Mikeyhend]

Chip,

Thanks, the code works fine,

but doesn't solve my problem.

I was trying to put a combobox in a cell on doubleclick.

In the example code I have, a line to create the combobox click event
is written in the VBE after the combobox is put on the screen.

I needed the code to hide the screen flickering when the VBE is
switched on and back off, but when I put 'unfreezeWindow' line after
the 'CreateEventProc' for the Combobox click event it raises an error.

Is there a solution for this problem?

Thanks
Mike

 

[Chip Pearson]

You need to lock the VBA Editor's main window, not an Excel
Window object. Try code like the following:

Private Declare Function FindWindow Lib "user32" Alias
"FindWindowA" _
(ByVal ClassName As String, ByVal WindowName As String) As
Long
Private Declare Function LockWindowUpdate Lib "user32" _
(ByVal hWndLock As Long) As Long

Sub TestIt()
Dim VBCodeMod As Object
Dim VBEHwnd As Long
Application.VBE.MainWindow.Visible = False
VBEHwnd = FindWindow("wndclass_desked_gsk", _
Application.VBE.MainWindow.Caption)
If VBEHwnd Then
LockWindowUpdate VBEHwnd
End If
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
VBCodeMod.InsertLines _
VBCodeMod.CreateEventProc("Click", "CommandButton1") + 1, _
"Msgbox ""OK"""
Application.VBE.MainWindow.Visible = False

LockWindowUpdate (0&)

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Mikeyhend"
message
news:[email protected]...

 

[Mikeyhend]

Chip,

Thanks, this one does the trick.

Mike