I need to be able to detect the event when someone
minimizes or restores the Excel application. How
can I do this?? (if it's even possible)

Thanks folks!

Looks you can't detect the Excel main window resize, only the workbook
window resize.
I would think it is possible. Maybe with a VB6 ActiveX exe.
You could ask in the classic VB (VB6) group:
microsoft.public.vb.general.discussion

RBS


"Robert Crandal" wrote in message
...
I need to be able to detect the event when someone
minimizes or restores the Excel application. How
can I do this?? (if it's even possible)

Thanks folks!

OK, this is in fact reasonably simple with the VBA method
Application.OnTime.
To demonstrate this all you need is a userform with only this code in it:

Option Explicit

Private Sub UserForm_Terminate()
TimerOff
End Sub


Then there is a module with all this code:

Option Explicit
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Private Declare Function GetWindowThreadProcessId _
Lib "user32" (ByVal hwnd As Long, _
ByRef lpdwProcessId 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
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function IsIconic Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function SetParent Lib "user32" (ByVal hWndChild As Long, _
ByVal hWndNewParent As
Long) As Long

Private bTimerEnabled As Boolean
Private dTimerInterval As Double
Private lExcelHwnd As Long
Private lFormHwnd As Long
Private lExcelWindowState As Long
Private lExcelWindowStatePrevious As Long

Sub LoadForm()

Load UserForm1
UserForm1.Show 0

lExcelHwnd = GetExcelHwnd()
lFormHwnd = GetFormHwnd(UserForm1.Caption)

bTimerEnabled = True
dTimerInterval = TimeValue("00:00:01")
lExcelWindowStatePrevious = -1

'start the timer
RunTimer

End Sub

Sub TimerOff()
bTimerEnabled = False
End Sub

Sub SetFormParent()

lExcelWindowState = IsIconic(lExcelHwnd)

If lExcelWindowState < lExcelWindowStatePrevious Then
If lExcelWindowState = 0 Then
SetParent lFormHwnd, lExcelHwnd
Else
SetParent lFormHwnd, 0
End If
End If

lExcelWindowStatePrevious = lExcelWindowState

End Sub

Sub RunTimer()

SetFormParent

If bTimerEnabled Then
Application.OnTime (Now + dTimerInterval), "RunTimer"
End If

End Sub

Function GetExcelHwnd() As Long

'------------------------------------------------------------
'Finds a top-level window of the given class and
'caption that belongs to this instance of Excel,
'by matching the process IDs
'Arguments: sClass The window class name to look for
' sCaption The window caption to look for
'Returns: Long The handle of Excel's main window
'------------------------------------------------------------
Dim hWndDesktop As Long
Dim hwnd As Long
Dim hProcThis As Long
Dim hProcWindow As Long
Dim sClass As String
Dim sCaption As String

If Val(Application.Version) = 10 Then
GetExcelHwnd = Application.hwnd
Exit Function
End If

sClass = "XLMAIN"
sCaption = Application.Caption

'All top-level windows are children of the desktop,
'so get that handle first
hWndDesktop = GetDesktopWindow

'Get the ID of this instance of Excel, to match
hProcThis = GetCurrentProcessId

Do
'Find the next child window of the desktop that
'matches the given window class and/or caption.
'The first time in, hWnd will be zero, so we'll get
'the first matching window. Each call will pass the
'handle of the window we found the last time, thereby
'getting the next one (if any)
hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption)

'Get the ID of the process that owns the window we found
GetWindowThreadProcessId hwnd, hProcWindow

'Loop until the window's process matches this process,
'or we didn't find the window
Loop Until hProcWindow = hProcThis Or hwnd = 0

'Return the handle we found
GetExcelHwnd = hwnd

End Function

Function GetFormHwnd(strCaption As String) As Long
If Val(Application.Version) = 9 Then
GetFormHwnd = FindWindow("ThunderDFrame", strCaption)
Else
GetFormHwnd = FindWindow("ThunderXFrame", strCaption)
End If
End Function


In the sheet make a button that runs the Sub LoadForm
Press that button and minimize Exel and bring it back again.
I am sure it can all be refined, but these are the basics and it works well
with me.


RBS




"Robert Crandal" wrote in message
...
I need to be able to detect the event when someone
minimizes or restores the Excel application. How
can I do this?? (if it's even possible)

Thanks folks!

OK, spoke to soon and seen situations where this doesn't work well at
all.
Will post something better later.

RBS


In the sheet make a button that runs the Sub LoadForm
Press that button and minimize Exel and bring it back again.
I am sure it can all be refined, but these are the basics and it works well
with me.

RBS

"Robert Crandal" wrote in message

...

I need to be able todetectthe event when someone
minimizes or restores the Excel application. *How
can I do this?? *(if it's even possible)

Thanks folks!

Some changes (thanks to Peter T) and now it works all fine:

In the userform:
------------------
Option Explicit

Private Sub UserForm_Terminate()
TimerOff
End Sub


In the normal module:
-------------------------
Option Explicit
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Private Declare Function GetWindowThreadProcessId _
Lib "user32" (ByVal hwnd As Long, _
ByRef lpdwProcessId 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
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function IsIconic Lib "user32" (ByVal hwnd As Long) As Long

Private Declare Function SetWindowLongA Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Const GWL_HWNDPARENT As Long = -8

Private bTimerEnabled As Boolean
Private dTimerInterval As Double
Private lExcelHwnd As Long
Private lFormHwnd As Long
Private lExcelWindowState As Long
Private lExcelWindowStatePrevious As Long

Sub LoadForm()

Load frmBart
UserForm1.Show 0

'set all the relevant private variables
lExcelHwnd = GetExcelHwnd()
lFormHwnd = GetFormHwnd(UserForm1.Caption)
lExcelWindowStatePrevious = 0
bTimerEnabled = True
dTimerInterval = TimeSerial(0, 0, 1)

'start the timer
RunTimer

End Sub

Sub TimerOff()
bTimerEnabled = False
End Sub

Sub SetFormParent()

lExcelWindowState = IsIconic(lExcelHwnd)

If lExcelWindowState < lExcelWindowStatePrevious Then
If lExcelWindowState = 0 Then
SetWindowLongA lFormHwnd, GWL_HWNDPARENT, lExcelHwnd
Else
SetWindowLongA lFormHwnd, GWL_HWNDPARENT, 0&
End If
lExcelWindowStatePrevious = lExcelWindowState

'seems needed this
'-----------------
UserForm1.Hide
UserForm1.Show vbModeless
End If

End Sub

Sub RunTimer()

SetFormParent

If bTimerEnabled Then
Application.OnTime (Now + dTimerInterval), "RunTimer"
End If

End Sub

Function GetExcelHwnd() As Long

'------------------------------------------------------------
'Finds a top-level window of the given class and
'caption that belongs to this instance of Excel,
'by matching the process IDs
'Arguments: sClass The window class name to look for
' sCaption The window caption to look for
'Returns: Long The handle of Excel's main window
'------------------------------------------------------------
Dim hWndDesktop As Long
Dim hwnd As Long
Dim hProcThis As Long
Dim hProcWindow As Long
Dim sClass As String
Dim sCaption As String

If Val(Application.Version) = 10 Then
GetExcelHwnd = Application.hwnd
Exit Function
End If

sClass = "XLMAIN"
sCaption = Application.Caption

'All top-level windows are children of the desktop,
'so get that handle first
hWndDesktop = GetDesktopWindow

'Get the ID of this instance of Excel, to match
hProcThis = GetCurrentProcessId

Do
'Find the next child window of the desktop that
'matches the given window class and/or caption.
'The first time in, hWnd will be zero, so we'll get
'the first matching window. Each call will pass the
'handle of the window we found the last time, thereby
'getting the next one (if any)
hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption)

'Get the ID of the process that owns the window we found
GetWindowThreadProcessId hwnd, hProcWindow

'Loop until the window's process matches this process,
'or we didn't find the window
Loop Until hProcWindow = hProcThis Or hwnd = 0

'Return the handle we found
GetExcelHwnd = hwnd

End Function

Function GetFormHwnd(strCaption As String) As Long

If Val(Application.Version) = 9 Then
GetFormHwnd = FindWindow("ThunderDFrame", strCaption)
Else
GetFormHwnd = FindWindow("ThunderXFrame", strCaption)
End If

End Function


Start by running LoadForm()


RBS


wrote in message
...
OK, spoke to soon and seen situations where this doesn't work well at
all.
Will post something better later.

RBS


In the sheet make a button that runs the Sub LoadForm
Press that button and minimize Exel and bring it back again.
I am sure it can all be refined, but these are the basics and it works
well
with me.

RBS

"Robert Crandal" wrote in message

...

I need to be able todetectthe event when someone
minimizes or restores the Excel application. How
can I do this?? (if it's even possible)

Thanks folks!

Correction, make that:

Sub LoadForm()

Load UserForm1


RBS


"RB Smissaert" wrote in message
...
Some changes (thanks to Peter T) and now it works all fine:

In the userform:
------------------
Option Explicit

Private Sub UserForm_Terminate()
TimerOff
End Sub


In the normal module:
-------------------------
Option Explicit
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Private Declare Function GetWindowThreadProcessId _
Lib "user32" (ByVal hwnd As Long, _
ByRef lpdwProcessId 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
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function IsIconic Lib "user32" (ByVal hwnd As Long) As
Long

Private Declare Function SetWindowLongA Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Const GWL_HWNDPARENT As Long = -8

Private bTimerEnabled As Boolean
Private dTimerInterval As Double
Private lExcelHwnd As Long
Private lFormHwnd As Long
Private lExcelWindowState As Long
Private lExcelWindowStatePrevious As Long

Sub LoadForm()

Load frmBart
UserForm1.Show 0

'set all the relevant private variables
lExcelHwnd = GetExcelHwnd()
lFormHwnd = GetFormHwnd(UserForm1.Caption)
lExcelWindowStatePrevious = 0
bTimerEnabled = True
dTimerInterval = TimeSerial(0, 0, 1)

'start the timer
RunTimer

End Sub

Sub TimerOff()
bTimerEnabled = False
End Sub

Sub SetFormParent()

lExcelWindowState = IsIconic(lExcelHwnd)

If lExcelWindowState < lExcelWindowStatePrevious Then
If lExcelWindowState = 0 Then
SetWindowLongA lFormHwnd, GWL_HWNDPARENT, lExcelHwnd
Else
SetWindowLongA lFormHwnd, GWL_HWNDPARENT, 0&
End If
lExcelWindowStatePrevious = lExcelWindowState

'seems needed this
'-----------------
UserForm1.Hide
UserForm1.Show vbModeless
End If

End Sub

Sub RunTimer()

SetFormParent

If bTimerEnabled Then
Application.OnTime (Now + dTimerInterval), "RunTimer"
End If

End Sub

Function GetExcelHwnd() As Long

'------------------------------------------------------------
'Finds a top-level window of the given class and
'caption that belongs to this instance of Excel,
'by matching the process IDs
'Arguments: sClass The window class name to look for
' sCaption The window caption to look for
'Returns: Long The handle of Excel's main window
'------------------------------------------------------------
Dim hWndDesktop As Long
Dim hwnd As Long
Dim hProcThis As Long
Dim hProcWindow As Long
Dim sClass As String
Dim sCaption As String

If Val(Application.Version) = 10 Then
GetExcelHwnd = Application.hwnd
Exit Function
End If

sClass = "XLMAIN"
sCaption = Application.Caption

'All top-level windows are children of the desktop,
'so get that handle first
hWndDesktop = GetDesktopWindow

'Get the ID of this instance of Excel, to match
hProcThis = GetCurrentProcessId

Do
'Find the next child window of the desktop that
'matches the given window class and/or caption.
'The first time in, hWnd will be zero, so we'll get
'the first matching window. Each call will pass the
'handle of the window we found the last time, thereby
'getting the next one (if any)
hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption)

'Get the ID of the process that owns the window we found
GetWindowThreadProcessId hwnd, hProcWindow

'Loop until the window's process matches this process,
'or we didn't find the window
Loop Until hProcWindow = hProcThis Or hwnd = 0

'Return the handle we found
GetExcelHwnd = hwnd

End Function

Function GetFormHwnd(strCaption As String) As Long

If Val(Application.Version) = 9 Then
GetFormHwnd = FindWindow("ThunderDFrame", strCaption)
Else
GetFormHwnd = FindWindow("ThunderXFrame", strCaption)
End If

End Function


Start by running LoadForm()


RBS


wrote in message
...
OK, spoke to soon and seen situations where this doesn't work well at
all.
Will post something better later.

RBS


In the sheet make a button that runs the Sub LoadForm
Press that button and minimize Exel and bring it back again.
I am sure it can all be refined, but these are the basics and it works
well
with me.

RBS

"Robert Crandal" wrote in message

...

I need to be able todetectthe event when someone
minimizes or restores the Excel application. How
can I do this?? (if it's even possible)

Thanks folks!