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.

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

www.OfficeArticles.com
www.MyExpertsOnline.com


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

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

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

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.

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.

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.

Nope, but for some it beats programming.

BTW another fix occurred to me. use a special character like ¦ (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("¦";Myvalue) should then represent a bar in just one cell. It
looks cool on my excel btw

DM Unseen

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

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 .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

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

DM Unseen

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?



Code:
--------------------
Function ProgressBar(percent_fill)
Dim cell As Range
Dim sh As Shape
Set cell = Application.Caller
cell.Select
x = 0 'Used to force condition for now

'Set font color
cell.Font.Color = RGB(255, 255, 255)

'Calculate percentage of cell to fill in
cwidth = cell.width * percent_fill

'Check if a shape is already present (How do I test for the shape?)
If x = 0 Then

'If not create the shape to desired width (This seems to work)
ActiveSheet.Shapes.AddShape(msoShapeRectangle, cell.Left, cell.Top, cwidth, cell.Height).Select
With Selection
.ShapeRange.Fill.ForeColor.SchemeColor = 30
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.Solid
.Name = "PB" & cell.Name
End With

Else
'If so, alter the width of the existing shape (Not working)
Set sh = ActiveSheet.Shapes("PB" & cell.Name)
sh.width = 0
sh.width = cwidth
End If

End Function
--------------------


Any help would be appreciated.


--
markyharris
------------------------------------------------------------------------
markyharris's Profile: http://www.excelforum.com/member.php...o&userid=24823
View this thread: http://www.excelforum.com/showthread...hreadid=381866