Progress Bar

[Paul W Smith]

Is it possible to use a progress bar on a worksheet?

I have added the Microsoft ProgressBar Control version 6.0 to my Controls
list in Excel 2007. I can insert the control onto my worksheet but I can
find no way of controling it, or referencing it for that matter.

I am not interested in using the control bar on a userform, that would be
too easy and not waht my deliverable requires!

Paul Smith

 

[Bob Phillips]

Yeah, it is really dumb to do something that is easy isn't it?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Eduardo]

Hi Paul,
enter progress bar in search in Excel programing and you will get different
options

 

[Paul W Smith]

With respect, what sort of answer/assistance is that?

I had hoped I had formulated my question respectfully enough to be taken
seriously. Yes there are alternatives that the people who wrote my
requirement, for reasons of their own, did not wish to use. I was asked if
a progress bar could be used on a worksheet. I tried and could not
reference it, so thought someone more knowledgable than I might offer some
sage advice.

So to ask again, is it possible to control a Microsoft ProgressBar Control
version 6.0, placed on a worksheet, from within running VBA code - if so how
is the control referenced?

Paul Smith

 

[Paul W Smith]

I am not too sure what you mean by "in Excel Programming", my news reader
does not allow searching.

I have searched in goggle and all references seem to be for using the
control on a user form.

If you have some references to what I am trying to do, please share.

Paul Smith

 

[Eduardo]

Hi Paul,
when you go to the discussion groups you have a window to enter the words to
be search, beside there is another window where you can choose from different
groups, let' say excel, new users, and excel programing. If you don't see
this windows go to the news group and just search for Progress Bar, I did and
you will find like 30 different answers, I wanted you to go there so you can
decide which one works better for you

hope this helps now

 

[Eduardo]

Hi Paul,
I did for you here is the link to the page with the different options

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

 

[john]

Paul,
have a look at this site & see if it helps you.

http://xcelfiles.homestead.com/ProgressBar.html

 

[EricG]

First of all, some of you responders are pretty rude. Not had your coffee
today?

Secondly, yes it is possible. Go into Design Mode and add a Progress
Control 6.0 object to a worksheet. Get out of Design Mode. Paste the
following code into a general module. Run Macro1. It should cause the
progress control to show status. The Status Bar will show what value the
progress control should be showing. Adapt to your needs.

You can set the properties of the control in Design Mode. Right click on
it, and select Properties. Set Min, Max, etc to whatever you need them to be.

HTH,

Eric

'
' Macro1 Macro
' Macro recorded 12/3/2009 by Eric Gundersen
'
Sub Macro1()
Dim i As Long
'
For i = 1 To 100
ActiveSheet.OLEObjects("ProgressBar1").Object.Value = i
Application.Wait Now + TimeValue("0:00:01") ' Wait one second...
Application.StatusBar = "i = " & Format(i, "000")
Next i
Application.StatusBar = False ' Resets the status bar to normal mode
End Sub

 

[RB Smissaert]

How about something simple like this:

Sub ShowProgressBar(lCounter As Long, _
lMax As Long, _
lInterval As Long, _
Optional lWidth As Long = 100, _
Optional strText As String)

Dim lStripes As Long

If lWidth = 0 Then
lWidth = 100
End If

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * lWidth, 0)
DoEvents
Application.StatusBar = strText & _
String(lStripes, "|") & _
String(lWidth - lStripes, ".") & "|"
End If

End Sub


Sub test()

Dim i As Long

For i = 1 To 10000000
ShowProgressBar i, 10000000, 100000, , String(8, " ")
Next i

End Sub


RBS

 

[Paul W Smith]

Eric,

Thanks for taking the time to furnish me with a solution.

I tried Activesheet.OLEObjects("ProgressBar1").value but did not know I had
to add '.object' to correctly reference the control.

Many thanks for help.

Paul Smith

 

[Peter T]

Reference the control in the same way you would any other worksheet control,
and code it as you would in a userform, eg

Sub test()
Dim i As Long, nextT As Single
Dim pb As ProgressBar

Set pb = ActiveSheet.OLEObjects("ProgressBar21").Object

pb.Value = 0
pb.Visible = True

For i = 1 To 100
nextT = Timer + 0.05
While Timer < nextT
Wend
nextT = Timer
pb.Value = i
Next
pb.Visible = False

End Sub

Difficult to imagine why your "deliverable", whatever that is, wouldn't
prefer it on a Userform. If you really want it on a sheet there are simpler
ways, eg lengthen a rectangle. Or, if you don't like things "easy" IIRC
Stephen Bullen has a demo on his site to draw a progress bar on a sheet (ie
no control object)

Regards,
Peter T

 

[Paul W Smith]

Edwardo,

You are making the assumption that everyone is using a newsreader that
allows searching.

I use Outlook Express to communicate with newsgroups and to my knowledge
there is no way to search, primarily because it only downloads a post when
you click on it, otherwise with the amount of groups I subscribe to my HD
woulf fill up in no time.

I appreciate you taking the time to reply to me.

Paul Smith

 

[Paul W Smith]

Great link.... I will look into using this interface!

Paul Smith