Bar Graphs In Cells
I just saw a future
conditional format coming up in Excel 12 via the
J-Walk Blog and thought I would try to imitate it. Note: This should work fine with Excel 2002 and 2003, I'm not sure about earlier versions but you can give it a try. (The transparency feature might not work as well though)
Here's the real McCoy... (picture from David Gainer's
Microsoft Excel blog)
And here's the code I used with Autoshapes...
Sub BarGraphsInCells()
On Error Resume Next
Dim LoopCount As Long, BarWidth As Long, Cell As Range, theRange As Range
Set theRange = Selection
LoopCount = 1
For Each Cell In theRange
BarWidth = (ActiveCell.Value / Application.Max(theRange)) * ActiveCell.Width * 0.7 ' Adjust to suit
ActiveSheet.Shapes.AddShape(msoShapeRectangle, ActiveCell.Left, ActiveCell.Top, BarWidth, ActiveCell.Height). _
Select
With Selection
.ShapeRange.Fill.ForeColor.SchemeColor = 12
.ShapeRange.Line.Visible = msoFalse
.ShapeRange.Fill.Transparency = 0.8
.Name = "The Bars" & LoopCount
End With
ActiveSheet.Shapes.Range(Array("The Bars", "The Bars" & LoopCount)).Select
Selection.ShapeRange.Group.Select
Selection.Name = "The Bars"
Cell.Offset(1).Select
LoopCount = LoopCount + 1
Next
theRange.Select
End Sub
And the result...
Hmm, not too bad considering it's way past my bedtime. Night!
Posted by andrewe at 01:10
Hi Andrew,
Both Excel 97 and 2K show 'semi-transparent' BarGraphs. If you would like to see an example:
http://scriptorium.serve-it.nl/examples/Transparent_bargraphs.gif
Yet another reason for me to get me an Office 12 license as soon as it becomes available.
Rembo
Hi Rembo,
Thanks for the image file :-)
We've got a computer with 2000 at work which I use for "testing" on occasion and hopefully I'm going to get hold of one with 97 sometime soon. Unfortunately transparency isn't as good with these earlier versions, though I believe if it's available, then make the most of it!
That said, I can't wait to work with Excel 12 either, it will be great to see these new features ;-)
Andrew, good job! it worked.
FYI, Office 12 has many other functions....I'd like tell you but it's still under NDA!
Two weeks ago I wrote a similar macro for a client, to compare values within a table. Last week I saw this feature demonstrated at the MVP Summit, and realized it's now built into conditional formatting. There are a lot of neat things in Excel 12.
Hi Colo and Jon!
Comments limit the number of words I can write but please have a look at my new post!
NDA...well I can respect that...guess I'll just have to wait to see what other goodies there are, reading David Gainer's blog is becoming more and more interesting!
Another way to do a similar thing is using a horizontal bar chart. By adjusting sizing properties of the bar chart to match cell sizes, and fixing the chart to the vertical range containing the data, you get the same look.
The upside of that approach is it doens't require VBA and any chart formatting options are fair game.
The downside is that the chart isn't quite "in" the cell, or behind the data for space efficiency as with Excel 2007.
Hard to argue with something built-in, ...some of the time anyway.
All the same, rolling-my-own is kind of fun ;-)
thanks for the nice code....
Hi Andrew,
This is a great website to share knowledge about VBA in Excel.
I am a big fans of excel but i am still a newbie in VBA,
I would like ask permission from you to use your code in my spreadsheet...
I hope the answer is yes... :-))
thanks for your reply
'Wish the best for you in this year
OJ, you're welcome :-)
Hi Ferdi,
No problem using the code, please be my guest.
All the best for you this year too :-)