How to make API call to freeze excel window?

M

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
 
C

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]...
 
C

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]...
 
C

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
 
M

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
 
C

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top