Flash the TaskBar

[gmulhall]

Hi,

I have an Excel VBA app which runs a timer and then pops up a userform. When the form pops up I'd like the excel taskbar window to flash to get the users attention. App is Excel 2003 running on Win XP.

I found the following code - but cannot make it work. Is it correct and where sould it be placed ? In a module ? In the userform ? In the timer proc ? Some combination ?

Thanks as always.

Option Explicit
 
Private Type FLASHWINFO
cbSize As Long
Hwnd As Long
dwFlags As Long
uCount As Long
dwTimeout As Long
End Type
Private Const FLASHW_TRAY = 2
Private Declare Function LoadLibrary Lib "kernel32" _
Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
Private Declare Function GetProcAddress Lib "kernel32" _
(ByVal hModule As Long, ByVal lpProcName As String) As Long
Private Declare Function FreeLibrary Lib "kernel32" _
(ByVal hLibModule As Long) As Long
Private Declare Function FlashWindowEx Lib "user32" _
(FWInfo As FLASHWINFO) As Boolean
 
Public Sub FlashWindow(Hwnd As Long, _
Optional NumberOfFlashes As Integer = 5)
'Purpose: Flashes a Window in the taskbar in order to notify
'a user of an event within a program
'Parameters: Hwnd=hwnd of frm to flash
'NumberofFlashes = Number of times to
'flash
'Notes: WINDOWS 98 OR 2000 is REQUIRED
'Uses FlashWindowEx API, which substitutes
'for bringing you window to the foreground
'obtrusively (e.g., on startup or when siginficant
'event occurs in your program) Windows 98/2000 no
'longer permits this
'Example:
'FlashWindow me.hwnd
'Prevent Errors by checking if
'the API function is available on the
'Current OS
If Not APIFunctionPresent("FlashWindowEx", "user32") _
Then Exit Sub
Dim bRet As Boolean
Dim udtFWInfo As FLASHWINFO
With udtFWInfo
.cbSize = 20
.Hwnd = Hwnd
.dwFlags = FLASHW_TRAY
.uCount = NumberOfFlashes 'flash window 5 times
.dwTimeout = 0
End With
bRet = FlashWindowEx(udtFWInfo)
End Sub

Private Function APIFunctionPresent(ByVal FunctionName _
As String, ByVal DllName As String) As Boolean
'USAGE:
'Dim bAvail as boolean
'bAvail = APIFunctionPresent("GetDiskFreeSpaceExA", "kernel32")
Dim lHandle As Long
Dim lAddr As Long
lHandle = LoadLibrary(DllName)
If lHandle <> 0 Then
lAddr = GetProcAddress(lHandle, FunctionName)
FreeLibrary lHandle
End If
APIFunctionPresent = (lAddr <> 0)
End Function

[Bob Phillips]

You don't seem to be getting a handle.

Do you have more code?

[Kenneth Hobs]

Is the Userform not enough to get the user's attention? Is focus the real issue?

Most people use a Userform, MsgBox or a timed MsgBox as the StatusBar message may be unnoticed.

We could flash the Excel button but Excel's statusbar would not flash. I guess you could change the text at intervals to simulate a sort of flashing.

Here is how to flash the excel application's button using your code:
1. Add to your API module:

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
  (ByVal lpClassName As String, ByVal lpWindowName As String) As Long[/vba] 2. In a that Module or another:
[vba]Sub Test_FlashWindow()
  Application.StatusBar = "Testing FlashWindow in StatusBar..."
  'FlashWindow FindWindow("Excel4", vbNullString) 'Statusbar's classname
  FlashWindow FindWindow("xlmain", vbNullString) 'Excel's main classname
  Application.StatusBar = False
End Sub

You will notice that the Statusbar message goes so fast that you don't even see it.

You may not even see your Window's Taskbar to see the button flash if you have it set to Auto-Hide.

Here is the flash statusbar message example. The 2nd run beeps at each display.

Sub Test_FlashStatusBar()
  FlashStatusBar "Hello World!"
  FlashStatusBar "Hello World!", True
End Sub

Sub FlashStatusBar(msg As String, Optional bBeep = False, Optional nLoops As Integer = 5)
  Dim i As Integer
  For i = 1 To nLoops
    With Application
      If bBeep Then Beep
      .StatusBar = msg
      .Wait (Now() + TimeValue("00:00:01"))
      .StatusBar = False
      .Wait (Now() + 1.15740740740741E-05) 'Same interval as above
    End With
  Next i
End Sub

[gmulhall]

You don't seem to be getting a handle.

Do you have more code?

Thanks xld. The code was taken off the web and is all that was provided. Perhaps you have an example ?

Thanks again,

Geoff

[gmulhall]

Hi Ken,

I'm looking to flash the Windows taskbar not the Excel status bar so that when Excel is minimised and the timer pops my user form there is an indication to the user to take an action.

Geoff

[mdmackillop]

Hi Geoff
Please don't quote large sections/whole posts, only those sections relevant to the reply

[Kenneth Hobs]

You may not even see your Window's Taskbar to see the button flash if you have it set to Auto-Hide.

I still don't see the problem. Maximize your useform so it will be obvious to the user that they need to interact with it.

[gmulhall]

Hi Ken,

The user does not get to see the form if they are working in another app when the userform pops. That's why I'd like to flash the Excel taskbar window. This draws there attention without immediately switching to Excel and interupting their work in say Outlook.

Thanks again,

Geoff

[gmulhall]

Hi Geoff
Please don't quote large sections/whole posts, only those sections relevant to the reply

Apologies MD

[Bob Phillips]

Thanks xld. The code was taken off the web and is all that was provided. Perhaps you have an example ?

Not of flashwindow, I would never use that, but I can see that ther is no code there to get the handle that the procedure requires.

[GTO]

Howdy,

I must be missing something, but when I have a userform 'pop' and Excel doesn't have the focus, Excel's icon in the Taskbar automatically starts flashing w/o any code. Mind you, I'm on my "not worth a pail of hot spit" laptop w/xl2000, but I am really thinking its the same in 2003.

Tested a bank/new uf with:

Sub callit()
    Application.OnTime Now() + TimeValue("00:00:10"), "called"
    'Application.WindowState = xlMinimized
End Sub

Sub called()
    UserForm1.Show
End Sub

...and buried excel under a couple of other windows.

Mark

[Kenneth Hobs]

As GTO notes, it should not be needed.

You could tell the user, play a sound or beep them too.

Sub called()
    Beep
    Application.Speech.Speak "Userform1 is ready!"
    UserForm1.Show
End Sub

[gmulhall]

Hi & thanks again gents.

Taskbar flashing only seems to work sometimes - and I've not tried to determine the different scenarios - but it can't be relied on I've found. That's why I'd like to use an API to trigger it and be in control. I have though of playing a sound but that only works if the user is at the PC - and if they're not their work collegues are not going to be too impressed with a repeated sound or voice - till the user returns.

Maybe someone can advise how I maximize Excel as interim solution please.

Thanks again,

Geoff

[gmulhall]

Hi,

I've resorted to

Application.Visible = True

to bring Excel to the front. Not very polite but effective until I get I can see if I can get the flashing taskbar working.

Geoff