Hi All,
I've had a look through previous posts, and discovered the Microsoft
ProgressBar Control, which looks fine for what I need, but I can't find
any useful support on it from anywhere! How do I link it to my sub?

Thanks,


--
pianoman
------------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=549368

See:


http://groups.google.com/group/micro...4ae4a6a8c4d49a
--
Gary''s Student


"pianoman" wrote:


Hi All,
I've had a look through previous posts, and discovered the Microsoft
ProgressBar Control, which looks fine for what I need, but I can't find
any useful support on it from anywhere! How do I link it to my sub?

Thanks,


--
pianoman
------------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=549368

Thanks Gary's Student, but this isn't quite what I need... Th
ProgressBar Cntrol is a seperate entity that you can paste onto you
sheet, add text, whatever... the search you made will only allow me t
access the green status LED's in the bottom window, unless I hav
misread of course...

Thanks,

Garet

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54936

I would use this PB
http://www.enhanceddatasystems.com/E...rogressBar.htm

--
HTH

Bob Phillips

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

"pianoman" wrote in
message ...

Hi All,
I've had a look through previous posts, and discovered the Microsoft
ProgressBar Control, which looks fine for what I need, but I can't find
any useful support on it from anywhere! How do I link it to my sub?

Thanks,


--
pianoman
------------------------------------------------------------------------
pianoman's Profile:
http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=549368

Thanks for that Bob,
I did see your suggestion in a previous post, but I couldn't see ho
to use it... Where to I post my code? Presumably I have to sandwic
the longest part of the sub in there somewhere...?

I've copied the two modules into my project, as instructed on the we
site... now what?!

Thank you,
Garet

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54936

You have to call the PB at various points within your processes. That is
true with any PB, You will need to look at your code and see where to
install the PB interrupts.

Robin provides a demo, which you can look at to see how to use it.

--
HTH

Bob Phillips

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

"pianoman" wrote in
message ...

Thanks for that Bob,
I did see your suggestion in a previous post, but I couldn't see how
to use it... Where to I post my code? Presumably I have to sandwich
the longest part of the sub in there somewhere...?

I've copied the two modules into my project, as instructed on the web
site... now what?!

Thank you,
Gareth


--
pianoman
------------------------------------------------------------------------
pianoman's Profile:
http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=549368

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 ow
codes... maybe I lef tmy brain at home this morning, but this isn'
clicking at all today!

Thanks

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54936

I am afraid at this point I cannot tell you as it depends. You need to
identify points in your code where you can insert the call to the PB. This
will have to be frequent, and meaningful to get the PB moving in a smooth
fashion.
--
HTH

Bob Phillips

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

"pianoman" wrote in
message ...

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,
Thanks for sticking with me!

I must not have been clear in my last post... I understand I wil
have to insert 'interupts' in my code... that's fine, but how do
'Call' the relevant subs from my code. When I try to call subs in th
Progress module, it won't find them 'cause they're all private subs.

How do I call the bits I need to Call, and then what is it I need t
insert to add the interupts?

Thanks

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54936

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

Thanks Tom,
Ok, I'm starting to grasp bits of this function now! I'm a littl
confused about how I adapt the code below (part of the JWalk example
which wraps a couple of lines which repeat several thousand time in th
progress code, to fit my coding, which has a couple of looping section
but mainly works through in a linear fashion, without a single sectio
that I can integrate into an incrementing 'loop'.

I have 25 pages of code so can't really post it, but I'd be happy t
send it to someone if that would make it any clearer...


Counter = 1
RowMax = 100
ColMax = 25
For r = 1 To RowMax
For c = 1 To ColMax
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
PctDone = Counter / (RowMax * ColMax)
With UserForm1
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width
10)
End With
' The DoEvents statement is responsible for the form updating
DoEvents
Next r


Thank you,
Garet

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54936

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

OK, I see now.

You don 't call the subs, it is a class module, so you would invoke the
methods via the object, but Robin actually does it via a property. You set
the property with a value that will translate to the width. For example,
looking at the demo

Set PB = New clsProgBar

This initiates the PB class.

With PB

This sets a link to your PB object

.Title = "Enhanced Datasystems Progress Bar"
.Caption2 = "This is caption 2"
.Caption3 = "This is caption 3"
.Show

This initialises the PB and displays it, and then the next bit is your main
loop where you will repeatedly call the PB



For nCounter = 0 To 100

.Progress = nCounter
.Caption1 = "Progress message " & CStr(nCounter)

This is the meat, where you pass it your current progress index which the PB
class uses to update the PB meter.

Is that clear?



--
HTH

Bob Phillips

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

"pianoman" wrote in
message ...

Hi Bob,
Thanks for sticking with me!

I must not have been clear in my last post... I understand I will
have to insert 'interupts' in my code... that's fine, but how do i
'Call' the relevant subs from my code. When I try to call subs in the
Progress module, it won't find them 'cause they're all private subs.

How do I call the bits I need to Call, and then what is it I need to
insert to add the interupts?

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'
get onto this site for some reason last week...

Bob,
Sorry, but it's still double-dutch to me. I still don't understan
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
integrate it with my code to make it work?

I've since used a different approach so I can draw a line under th
project and hand it over... I just activate a new screen that state
'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, bu
I understand if you guys don't want to explain it all again i
novice-speak! :)

Thanks for all your help.

Regards,
Garet

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54936

What does the code that you have look like?

--
HTH

Bob Phillips

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

"pianoman" wrote in
message ...

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

Hi Bob,
I've attached the file in Pdf... the code starts with Su
Refreshcharts on Page 2.

I'm aware that it's not the tidiest of projects... it was the firs
thing I ever programmed so be gentle! :)

As you can see it's 42 pages long, and it's the sections starting a
page 27 that take the longest (opening files processing and copying)
There are three looping sections that do this...

Thanks Bob,

Gareth

Bob Phillips Wrote:
What does the code that you have look like?

--
HTH

Bob Phillips

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

"pianoman"
wrote in
message ...

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 no
perfect,
as there is no progress indication, and if it freezes, there's n
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


+-------------------------------------------------------------------
|Filename: Risk Charts.pdf
|Download: http://www.excelforum.com/attachment.php?postid=4884
+-------------------------------------------------------------------

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54936