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

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)

"Paul W Smith" wrote in message
...
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

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

"Paul W Smith" wrote:

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


.

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



"Bob Phillips" wrote in message
...
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)

"Paul W Smith" wrote in message
...
Is it possible to use a progress bar on a worksheet?

I have added the 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

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" wrote in message
...
Hi Paul,
enter progress bar in search in Excel programing and you will get
different
options

"Paul W Smith" wrote:

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


.

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

"Paul W Smith" wrote:

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



"Bob Phillips" wrote in message
...
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)

"Paul W Smith" wrote in message
...
Is it possible to use a progress bar on a worksheet?

I have added the 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






.

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

http://www.microsoft.com/office/comm...xp=&sloc=en-us


"Paul W Smith" wrote:

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



"Bob Phillips" wrote in message
...
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)

"Paul W Smith" wrote in message
...
Is it possible to use a progress bar on a worksheet?

I have added the 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






.

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

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


"Paul W Smith" wrote:

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



"Bob Phillips" wrote in message
...
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)

"Paul W Smith" wrote in message
...
Is it possible to use a progress bar on a worksheet?

I have added the 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






.

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.Valu e = 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


"Paul W Smith" wrote:

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


.

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" wrote in message
...
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

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



"EricG" wrote in message
...
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.Valu e = 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


"Paul W Smith" wrote:

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


.

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" wrote in message
...
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

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


"Eduardo" wrote in message
...
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

"Paul W Smith" wrote:

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



"Bob Phillips" wrote in message
...
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)

"Paul W Smith" wrote in message
...
Is it possible to use a progress bar on a worksheet?

I have added the 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






.

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

Paul Smith




"Eduardo" wrote in message
...
Hi Paul,
I did for you here is the link to the page with the different options

http://www.microsoft.com/office/comm...xp=&sloc=en-us


"Paul W Smith" wrote:

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



"Bob Phillips" wrote in message
...
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)

"Paul W Smith" wrote in message
...
Is it possible to use a progress bar on a worksheet?

I have added the 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






.