Progress Bar Stuck after Crashing

I'm using code like this to update the progress bar in excel:

Private Declare Function FindWindow& Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName$, ByVal lpWindowName$)
Private Declare Function CreateWindowEX& Lib "user32" Alias _
"CreateWindowExA" (ByVal dwExStyle&, ByVal lpClassName$ _
, ByVal lpWindowName$, ByVal dwStyle&, ByVal x&, ByVal y& _
, ByVal nWidth&, ByVal nHeight&, ByVal hWndParent& _
, ByVal hMenu&, ByVal hInstance&, lpParam As Any)
Private Declare Function DestroyWindow& Lib "user32" (ByVal hWnd&)
Private Declare Function SendMessage& Lib "user32" Alias _
"SendMessageA" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As
Any)
Private Declare Function GetClientRect& Lib "user32" _
(ByVal hWnd&, lpRect As RECT)
Private Declare Function FindWindowEx& Lib "user32" Alias _
"FindWindowExA" (ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1$, ByVal
lpsz2$)


Private Type RECT
cl As Long
ct As Long
cr As Long
cb As Long
End Type


Sub PBarDraw()
Dim BarState As Boolean
Dim hWnd&, pbhWnd&, y&, h&, i&, R As RECT
hWnd = FindWindow(vbNullString, Application.Caption)
hWnd = FindWindowEx(hWnd, ByVal 0&, "EXCEL4", vbNullString)
GetClientRect hWnd, R
h = (R.cb - R.ct) - 6: y = R.ct + 3
pbhWnd = CreateWindowEX(0, "msctls_progress32", "" _
, &H50000000, 35, y, 185, h, hWnd, 0&, 0&, 0&)
SendMessage pbhWnd, &H409, 0, ByVal RGB(0, 0, 125)
BarState = Application.DisplayStatusBar
Application.DisplayStatusBar = True
For i = 1 To 50000
DoEvents
Application.StatusBar = Format(i / 50000, "0%")
SendMessage pbhWnd, &H402, Val(Application.StatusBar), 0
Next i
DestroyWindow pbhWnd
Application.StatusBar = False
Application.DisplayStatusBar = BarState
End Sub


My problem is this: If the code crashes and and does not destroy the
pbhWnd, the progress bar stays dispalyed as is till I restart excel.
Is there anyway to relocate the window (since its created on the fly)
and destroy it in a seperate set of code that I could run anytime it
gets hung?

Thanks in advance,

John

.

Relevant Pages

  • Re: Shell Execute weirdness
    ... but window which displays spreadsheet itself appears ... (ByVal hwnd As Long, ByVal lpszOp As String, _ ... call the GetDesktopWindow Win32 API function. ... an alternative would be to use Automation with Excel. ...
    (microsoft.public.vb.general.discussion)
  • Re: hide name box
    ... Excel 2007 has this capability but I was working in Excel 2002. ... ' Enables or disables a window. ... (ByVal hwnd As Long, _ ... Private Declare Function GetDesktopWindow Lib "user32" As Long ...
    (microsoft.public.excel.programming)
  • Re: Check if form is open
    ... You can use the FindWindow API to get the hWnd of the form. ... (ByVal lpClassName As String, ByVal lpWindowName As String) ... Microsoft MVP - Excel ...
    (microsoft.public.excel.programming)
  • RE: Cannot get code to work for API Save Dialog Box
    ... 'Set filter to show only Excel spreadsheets ... With xlSheet ... Dim Hwnd As Long ... ' If Excel is running this API call returns its handle. ...
    (microsoft.public.access.forms)
  • Re: Obtain a Process ID
    ... of Excel, just check the value of the application's Hwnd property. ... Frank DeLuccia wrote: ...
    (microsoft.public.excel.programming)