Warning Box

[Looping through]

I have a reminder set to pop up based automatically when a certain criteria
is meet. I know how to do this, but if user has minimized covered up the
current workbook, how do I make the program flash or become the active
projram on there screen to notify them to do something.

Any Ideas?

 

[GTVT06]

I have a reminder set to pop up based automatically when a certain criteria
is meet. I know how to do this, but if user has minimized covered up the
current workbook, how do I make the program flash or become the active
projram on there screen to notify them to do something.

Any Ideas?

Add this to your code to Maximize the screen. Change the workbook name
accordingly.

Windows("Book1").WindowState = xlMaximized

 

[Howard Kaikow]

Rather than maximizing a window, which might interfere with whatever else
the user is doing, it may be better to use the FlashWindow (or
FlashWindowEx) API..

You could use the GetActiveWindow API to get the handle for the active Excel
window, then feed that handle to the FlashWindow (or FlashWindowEx) API at
the appropriate time.

 

[Howard Kaikow]

One could use the code below to start the flashing, but code would be needed
to turn off the flashing in response a user action.
In VB 6, I turn off suchflashung when the user moves the mouse over, or
clicks on, a control.
I guess, with Excel, you would want to stop the flashing when the user
maximizes Excel, so ypu would need to test in an application event.

Option Explicit
Private Const FLASHW_STOP = 0
Private Const FLASHW_CAPTION = &H1
Private Const FLASHW_TRAY = &H2
Private Const FLASHW_ALL = (FLASHW_CAPTION Or FLASHW_TRAY)
Private Const FLASHW_TIMER = &H4
Private Const FLASHW_TIMERNOFG = &HC

Private Type FLASHWINFO
cbSize As Long
hwnd As Long
dwFlags As Long
uCount As Long
dwTimeout As Long
End Type

Private Declare Function FlashWindow Lib "user32" (ByVal hwnd As Long,
ByVal bInvert As Long) As Long
Private Declare Function FlashWindowEx Lib "user32" (pfwi As FLASHWINFO)
As Boolean
Private Declare Function GetActiveWindow Lib "user32" () As Long

Public Sub FlashOnce()
Dim hwnd As Long
hwnd = GetActiveWindow()
FlashWindow hwnd, -1
End Sub

Public Sub KeepFlashing()
Dim FlashInfo As FLASHWINFO
Dim hwnd As Long

hwnd = GetActiveWindow()

FlashInfo.cbSize = Len(FlashInfo)
FlashInfo.dwFlags = FLASHW_ALL Or FLASHW_TIMER
FlashInfo.dwTimeout = 0
FlashInfo.hwnd = hwnd
FlashInfo.uCount = 0

FlashWindowEx FlashInfo
End Sub

 

[Howard Kaikow]

In a VB 6 form, here's what I do:

Define module scope variable:
Private blnStopFlash As Boolean

In the click event for the button that starts things running, include near
the beginning:

blnStopFlash = False
With FlashInfo
.cbSize = Len(FlashInfo)
.hwnd = Me.hwnd
.dwFlags = FLASHW_ALL Or FLASHW_TIMER
.dwTimeout = 0
.uCount = 0
End With

For Excel, GetActiveWindow has to be used instead of Me.hwnd

When the click event wants to signal that it is done, start the flashing by
using:

FlashWindowEx FlashInfo
blnStopFlash = True

Include the following Sub:

Private Sub StopFlash(FlashInfoLocal As FLASHWINFO)
If blnStopFlash Then
FlashInfoLocal.dwFlags = FLASHW_STOP
FlashWindowEx FlashInfoLocal
blnStopFlash = False
End If
End Sub

Wherever needed, e.g., in all Click and MouseDown events, include the
following:
StopFlash FlashInfo

If Excel is minimized, I guess that only the relevant application events
need to contain

StopFlash FlashInfo