Re: Progress Bar Stuck after Crashing

John,
As Excel (and Office apps in general) are complex apps that do not always
seem to behave as you expect with this kind of code, expect a lot of crashes
until you get it bullet proof.
Whilst I'm all for extending the capabilities of Excel, the end result has
to be worth it to the user.
Personally, I do not think is one of those cases.

At least you should add error checking; check the return value of the call
(or LastError if appropriate) rather than VBA error handling.

Also, the classnames of some of Excel's object are changed in 2007 version.
Not sure about the status bar, but you should check.

NickHK

"John" <john.fuller@xxxxxxxxxxxxxx> wrote in message
news:1181329084.365856.262370@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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: office documents wont "load"
    ... DDE should be enabled for Excel. ... associations for Excel since it looks like the default has been ... >> All Office apps closed, updated Acrobat, and the problem's back? ...
    (microsoft.public.office.misc)
  • Re: Access has no option to import data from excel
    ... Doug Steele, Microsoft Access MVP ... "Neil MacDonald" wrote in message ... > use the office Apps without issue. ... > import pop-up page, Excel is not listed. ...
    (microsoft.public.access.externaldata)
  • Re: Word shuts down
    ... I've had the same problem with all Office apps (Word, ... Excel, PowerPoint) in Office XP and Office 2003 ... MCO.DLL - they usually report error code 0xC0000005. ... it also disables autoloaded macros. ...
    (microsoft.public.word.application.errors)
  • "Save As" dialog taking a long time to display
    ... in trying to save a Word document using the "Save as.." ... it took several minutes to display the dialog box. ... Office apps (Entourage, Excel), but not Word. ...
    (microsoft.public.mac.office)
  • Re: Microsoft products are not working
    ... > reinstallation, which did not work. ... All Office apps ... > (Word, Excel, PowerPoint) will not open any files, says ...
    (microsoft.public.office.misc)