Progress Bar Control

this approach documented at John Walkenbach's site is extremely easy and
should help you understand the concepts.

http://www.j-walk.com/ss/excel/tips/tip34.htm

I haven't used Rob's code, but I wouldn't doubt it is based on the same
principle, but encased in a class module.

The important point is that the progress bar has no built in intelligence
(or timers) - you have to tell it (in your code) to update and to update to
what value at the appropriate points.

One caution on limitations: Many people would like to show a progress bar
when saving a file that takes a long time to save (as an example). since
this is a single command, there is no way the code can update a progress bar
during the save.

--
Regards,
Tom Ogilvy


"pianoman" wrote:

>
> Ok, so Code wise, I need to call the PB at the start of my code using
>
> call Show()
>
> then insert
>
> call updatestatus()
>
> at regular points in my code...?
>
> Something like that?
>
> I don't understand how to adapt the demo on the site to fit my own
> codes... maybe I lef tmy brain at home this morning, but this isn't
> clicking at all today!
>
> Thanks,
>
>
> --
> pianoman
> ------------------------------------------------------------------------
> pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
> View this thread: http://www.excelforum.com/showthread...hreadid=549368
>
>

Just out of curiosity - is there any way to use or access (in VBA code) the
progress indicator that displays on the status bar?

--
Bill @ UAMS


"Tom Ogilvy" wrote:

> this approach documented at John Walkenbach's site is extremely easy and
> should help you understand the concepts.
>
> http://www.j-walk.com/ss/excel/tips/tip34.htm
>
> I haven't used Rob's code, but I wouldn't doubt it is based on the same
> principle, but encased in a class module.
>
> The important point is that the progress bar has no built in intelligence
> (or timers) - you have to tell it (in your code) to update and to update to
> what value at the appropriate points.
>
> One caution on limitations: Many people would like to show a progress bar
> when saving a file that takes a long time to save (as an example). since
> this is a single command, there is no way the code can update a progress bar
> during the save.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "pianoman" wrote:
>
> >
> > Ok, so Code wise, I need to call the PB at the start of my code using
> >
> > call Show()
> >
> > then insert
> >
> > call updatestatus()
> >
> > at regular points in my code...?
> >
> > Something like that?
> >
> > I don't understand how to adapt the demo on the site to fit my own
> > codes... maybe I lef tmy brain at home this morning, but this isn't
> > clicking at all today!
> >
> > Thanks,
> >
> >
> > --
> > pianoman
> > ------------------------------------------------------------------------
> > pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
> > View this thread: http://www.excelforum.com/showthread...hreadid=549368
> >
> >

http://www.j-walk.com/ss/excel/files/developer.htm
Control the LED Display in the StatusBar
[This is the next to last entry on the page]

is a long complex method.


Michel Pierron posted this recently:

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

--
Regards,
Tom Ogilvy



"BillCPA" wrote:

> Just out of curiosity - is there any way to use or access (in VBA code) the
> progress indicator that displays on the status bar?
>
> --
> Bill @ UAMS
>
>
> "Tom Ogilvy" wrote:
>
> > this approach documented at John Walkenbach's site is extremely easy and
> > should help you understand the concepts.
> >
> > http://www.j-walk.com/ss/excel/tips/tip34.htm
> >
> > I haven't used Rob's code, but I wouldn't doubt it is based on the same
> > principle, but encased in a class module.
> >
> > The important point is that the progress bar has no built in intelligence
> > (or timers) - you have to tell it (in your code) to update and to update to
> > what value at the appropriate points.
> >
> > One caution on limitations: Many people would like to show a progress bar
> > when saving a file that takes a long time to save (as an example). since
> > this is a single command, there is no way the code can update a progress bar
> > during the save.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "pianoman" wrote:
> >
> > >
> > > Ok, so Code wise, I need to call the PB at the start of my code using
> > >
> > > call Show()
> > >
> > > then insert
> > >
> > > call updatestatus()
> > >
> > > at regular points in my code...?
> > >
> > > Something like that?
> > >
> > > I don't understand how to adapt the demo on the site to fit my own
> > > codes... maybe I lef tmy brain at home this morning, but this isn't
> > > clicking at all today!
> > >
> > > Thanks,
> > >
> > >
> > > --
> > > pianoman
> > > ------------------------------------------------------------------------
> > > pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
> > > View this thread: http://www.excelforum.com/showthread...hreadid=549368
> > >
> > >

Hi Bob/Tom,
Thanks for your replies... sorry I didn't reply sooner, I couldn't get onto this site for some reason last week...

Bob,
Sorry, but it's still double-dutch to me. I still don't understand what code I need to place where to integrate it with my own code.

Tom,
Similarly, using the status bar would be ok I guess, but how do I integrate it with my code to make it work?

I've since used a different approach so I can draw a line under the project and hand it over... I just activate a new screen that states 'Your report is running... Please wait' Obviously this is not perfect, as there is no progress indication, and if it freezes, there's no help, but at least it's calms peoples nerves initially!

I'd still be interested to learn how to do what I'm trying to do, but I understand if you guys don't want to explain it all again in novice-speak!

Thanks for all your help.

Regards,
Gareth

What does the code that you have look like?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"pianoman" <pianoman.29agez_1150114505.4904@excelforum-nospam.com> wrote in
message news:pianoman.29agez_1150114505.4904@excelforum-nospam.com...
>
> Hi Bob/Tom,
> Thanks for your replies... sorry I didn't reply sooner, I couldn't
> get onto this site for some reason last week...
>
> Bob,
> Sorry, but it's still double-dutch to me. I still don't understand
> what code I need to place where to integrate it with my own code.
>
> Tom,
> Similarly, using the status bar would be ok I guess, but how do I
> integrate it with my code to make it work?
>
> I've since used a different approach so I can draw a line under the
> project and hand it over... I just activate a new screen that states
> 'Your report is running... Please wait' Obviously this is not perfect,
> as there is no progress indication, and if it freezes, there's no help,
> but at least it's calms peoples nerves initially!
>
> I'd still be interested to learn how to do what I'm trying to do, but
> I understand if you guys don't want to explain it all again in
> novice-speak!
>
> Thanks for all your help.
>
> Regards,
> Gareth
>
>
> --
> pianoman
> ------------------------------------------------------------------------
> pianoman's Profile:
http://www.excelforum.com/member.php...o&userid=33712
> View this thread: http://www.excelforum.com/showthread...hreadid=549368
>