How do i get Excel 2003 to have a progression bar in the adjacent.
 

i use excel as a sales lead tracking tool. I have the probability of winning
in % and it keeps changing. How do i get excel 2003 to show me this visually
(in an adjacent cell) as a progression bar. Alternatively can i make excel
pick a value (in text form) from elsehere in the sheet?

Here's one way:

Use the Drawing Toolbar to put a rectangle over the cell where you want to see
the bar and give it a colored interior. Set it's properties to Move but Don't
size with Cells.

I'll assume its name is "Rectangle 1", and cell A2 contains the percentage you
want to track. Then put these routines in the code module for that worksheet.
Set cell A2 to the value 1 (or 100%), then modify the multiplier (I used 100)
so the width is what you want for full scale.

Private Sub Worksheet_Calculate()
RectangleWidth
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then RectangleWidth
End Sub

Private Sub RectangleWidth()
Me.DrawingObjects("Rectangle 1").Width = Range("A2").Value * 100
End Sub


On Mon, 7 Mar 2005 20:27:02 -0800, "Ravi"
wrote:

i use excel as a sales lead tracking tool. I have the probability of winning
in % and it keeps changing. How do i get excel 2003 to show me this visually
(in an adjacent cell) as a progression bar. Alternatively can i make excel
pick a value (in text form) from elsehere in the sheet?

Thanks Myrna,

Will try. Though i Should admit that i will be trying code module for the
first time

"Myrna Larson" wrote:

Here's one way:

Use the Drawing Toolbar to put a rectangle over the cell where you want to see
the bar and give it a colored interior. Set it's properties to Move but Don't
size with Cells.

I'll assume its name is "Rectangle 1", and cell A2 contains the percentage you
want to track. Then put these routines in the code module for that worksheet.
Set cell A2 to the value 1 (or 100%), then modify the multiplier (I used 100)
so the width is what you want for full scale.

Private Sub Worksheet_Calculate()
RectangleWidth
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then RectangleWidth
End Sub

Private Sub RectangleWidth()
Me.DrawingObjects("Rectangle 1").Width = Range("A2").Value * 100
End Sub


On Mon, 7 Mar 2005 20:27:02 -0800, "Ravi"
wrote:

i use excel as a sales lead tracking tool. I have the probability of winning
in % and it keeps changing. How do i get excel 2003 to show me this visually
(in an adjacent cell) as a progression bar. Alternatively can i make excel
pick a value (in text form) from elsehere in the sheet?

Thanks, Myrna !
Tried your suggestion, it's interesting and useful ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----