Progress Bar in Cell

[Ravi]

Hi,

I use excel to keep track of my sales leads. Ideally i would like a nearby
cell to show graphically the status of the lead when i enter the values
(conversion probability) in the adjacent cell.

Can someone help. I should probaly warn that i am no good with macros.

[Anne Troy]

Try this, Ravi:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=319
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Ravi" <Ravi@discussions.microsoft.com> wrote in message
news:B6D1E897-4A7F-4EB2-9B6D-76D0FD53CA18@microsoft.com...
> Hi,
>
> I use excel to keep track of my sales leads. Ideally i would like a nearby
> cell to show graphically the status of the lead when i enter the values
> (conversion probability) in the adjacent cell.
>
> Can someone help. I should probaly warn that i am no good with macros.

[Ravi]

Anne,

Thanks a ton. Will give it a go.

Ravi

"Anne Troy" wrote:

> Try this, Ravi:
> http://www.vbaexpress.com/kb/getarticle.php?kb_id=319
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "Ravi" <Ravi@discussions.microsoft.com> wrote in message
> news:B6D1E897-4A7F-4EB2-9B6D-76D0FD53CA18@microsoft.com...
> > Hi,
> >
> > I use excel to keep track of my sales leads. Ideally i would like a nearby
> > cell to show graphically the status of the lead when i enter the values
> > (conversion probability) in the adjacent cell.
> >
> > Can someone help. I should probaly warn that i am no good with macros.
>
>
>

[Bob Phillips]

How do you identify the status? If it is by the sales value, all you need is
a simple IF test.

--
HTH

Bob Phillips

"Ravi" <Ravi@discussions.microsoft.com> wrote in message
news:80213C8A-9445-48EA-9AD8-527EFE920D64@microsoft.com...
> Anne,
>
> Thanks a ton. Will give it a go.
>
> Ravi
>
> "Anne Troy" wrote:
>
> > Try this, Ravi:
> > http://www.vbaexpress.com/kb/getarticle.php?kb_id=319
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "Ravi" <Ravi@discussions.microsoft.com> wrote in message
> > news:B6D1E897-4A7F-4EB2-9B6D-76D0FD53CA18@microsoft.com...
> > > Hi,
> > >
> > > I use excel to keep track of my sales leads. Ideally i would like a
nearby
> > > cell to show graphically the status of the lead when i enter the
values
> > > (conversion probability) in the adjacent cell.
> > >
> > > Can someone help. I should probaly warn that i am no good with macros.
> >
> >
> >

[Patrick Molloy]

you might enjoy this demo....
copy code below. Run AddProgressBars once to initialise
run randomgrowth any number of times

Option Explicit

Sub AddProgressBars()
Dim index As Long
Dim cell As Range

For index = 1 To 5
Set cell = Cells(index + 5, 3)
cell.Select
ActiveSheet.Shapes.AddShape(msoShapeRectangle, cell.Left, cell.Top,
cell.width, cell.Height).Select
With Selection
.ShapeRange.Fill.ForeColor.SchemeColor = 48
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.Solid
.Name = "ProgressBar" & index
End With
Next
End Sub

Sub randomgrowth()
Dim index As Long
Dim width As Long
Dim sh As Shape
Dim done(1 To 5) As Long
Dim doneT As Long
resetbars
Dim tim As Long
Do
For index = 1 To 5
Set sh = ActiveSheet.Shapes("ProgressBar" & index)
width = sh.width
width = width + Rnd * 25
If width > Cells(index, 3).width Then
width = Cells(index, 3).width
If done(index) = 0 Then
done(index) = 1
doneT = doneT + 1
End If
End If
sh.width = width
tim = Timer
Next
Do: DoEvents: Loop Until Timer > tim + 2
Loop Until doneT >= 5

MsgBox "Done"
End Sub
Sub resetbars()
Dim index As Long
For index = 1 To 5
ActiveSheet.Shapes("ProgressBar" & index).width = 0
Next
End Sub




"Ravi" wrote:

> Hi,
>
> I use excel to keep track of my sales leads. Ideally i would like a nearby
> cell to show graphically the status of the lead when i enter the values
> (conversion probability) in the adjacent cell.
>
> Can someone help. I should probaly warn that i am no good with macros.

[DM Unseen]

I would suggest:

Use no macro's but use conditional formatting and several cells. FOr
each cell color it (red) when your data goes over a certain treshold
and increase the treshold for each adjectant cell. This could represent
your statusbar over several cells, where each cell is a bar in your
status bar.

[GB]

Of course if you also shrink the width of the cells used then you could have
a pretty decent bar, however you limit your ability to use the maximum column
width, however if you set this up in the rows such that the bar grows
vertically then you are less likely to "run out" of storage space. However,
not the "prettiest" fix.

"DM Unseen" wrote:

> I would suggest:
>
> Use no macro's but use conditional formatting and several cells. FOr
> each cell color it (red) when your data goes over a certain treshold
> and increase the treshold for each adjectant cell. This could represent
> your statusbar over several cells, where each cell is a bar in your
> status bar.
>
>

[DM Unseen]

Nope, but for some it beats programming.

BTW another fix occurred to me. use a special character like =A6 (I'm
not sure how this will show up and repeat this block character
depending on a value, and give it a pretty color:

=3DREPT("=A6";Myvalue) should then represent a bar in just one cell. It
looks cool on my excel btw

DM Unseen

[Tushar Mehta]

You may find the following much more useful. No programming needed and
it uses 1 to n cells (depending on how fine a gradation you want) in
the same row as where you enter the number.
Using Worksheet Cells to simulate a graph
http://www.tushar-
mehta.com/excel/newsgroups/worksheet_as_chart/index.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <80213C8A-9445-48EA-9AD8-527EFE920D64@microsoft.com>,
Ravi@discussions.microsoft.com says...
> Anne,
>
> Thanks a ton. Will give it a go.
>
> Ravi
>
> "Anne Troy" wrote:
>
> > Try this, Ravi:
> > http://www.vbaexpress.com/kb/getarticle.php?kb_id=319
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "Ravi" <Ravi@discussions.microsoft.com> wrote in message
> > news:B6D1E897-4A7F-4EB2-9B6D-76D0FD53CA18@microsoft.com...
> > > Hi,
> > >
> > > I use excel to keep track of my sales leads. Ideally i would like a nearby
> > > cell to show graphically the status of the lead when i enter the values
> > > (conversion probability) in the adjacent cell.
> > >
> > > Can someone help. I should probaly warn that i am no good with macros.
> >
> >
> >
>

[Tushar Mehta]

That's a nice idea. Of course, even nicer than | is a small solid
rectangle. Unicode value 25A0 but quicker with Insert | Symbol...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <1119613444.775635.197200@f14g2000cwb.googlegroups.com>,
dm.unseen@gmail.com says...
> Nope, but for some it beats programming.
>
> BTW another fix occurred to me. use a special character like =3F (I'm
> not sure how this will show up and repeat this block character
> depending on a value, and give it a pretty color:
>
> =REPT("=3F";Myvalue) should then represent a bar in just one cell. It
> looks cool on my excel btw
>
> DM Unseen
>
>

[DM Unseen]

That is exactly what I Inserted (unfortuantly not what showed up on
google! after copy/paste

DM Unseen

[markyharris]

The function below is altered using Patricks post above.

I need to display a bar depicting the percentage listed in a cell. I can pass a value between .1 and 1.0 and the bar will fill that percentage of the cell.

However, I want to test for the presence of a rectangle and alter the width of the existing rectangle rather than create another rectangle on top of an existing one.

I also want to change the font color to white so the "0" that normally displayed is effectively hidden.

I would appreciate any help with the following;

• How do I test for the presence of the shape in the cell.
• How do I change the width of the cell once I determine a rectangle already exists?
• How do I set the cell's font color to white to hide the "0" thats displayed?

Please Login or Register  to view this content.

Any help would be appreciated.