display dots inside a cell in graph form but without Excel graph?

[Rich]

I got a request from a user to display dots like a trend graph -- in a cell
adjacent to another cell in a row - for each of several rows. The dots don't
vary too much, and I was thinking I could draw these dots on VBA label
controls and place these labels in the cell for each row. Actually, I'm not
sure if I could draw dots inside of a VBA label control. My other
alternative would be to create a custom label in .Net an add that as a custom
control to Excel. Is there something simpler I could do within Excel before
I go crazy with .Net on this?

Thanks,
Rich

 

[Tim Williams]

You could just use shapes, but whatever you use they will not be "in" the
cell, which means you always have to be careful to reposition them if the
end-user (eg) sorts the data.

Tim

 

[Rick Rothstein]

Maybe the open source TinyGraphs add-in located here would be acceptable...

http://www.spreadsheetml.com/products.html

 

[Gord Dibben]

With a percentage in A1

Enter this in B1

=REPT(CHAR(149),A1*CELL("width",B1))


Gord Dibben MS Excel MVP

 

[Rich]

Thanks all for the replies. Here is what I would have the dots look like in
some container:

â— â— â— â—
â— â— â— â—
â— â— â— â—

I am thinking displaying these dots in a label or some shape object would be
the most logical thing. The rows are a few rows apart, and this report
would be printed out so not to worry about a user reordering the data. The
other catch is that I would like to automate the placing of these dots.

Maybe the tiny graph suggestion may be an option. Will have to look into
that. Otherwise, I could create a custom library (dll) control in C# to com
that could draw dots like this and add that lib to my excel proj, but ideally
I would like to avoid adding dependencies to the Excel proj.

 

[Tim Williams]

Should give you some ideas:

'*********************************
Sub Tester()

Const dotSz As Integer = 6
Dim c As Range, s As Shape, x As Integer

Set c = ActiveSheet.Range("A9") 'plot goes here

For x = 1 To 20
Set s = c.Parent.Shapes.AddShape(msoShapeOval, _
c.Left + (x * dotSz), c.Top + (Rnd() * c.Height), dotSz,
dotSz)
s.Fill.ForeColor.RGB = RGB(255, 0, 0)
s.Line.Visible = msoFalse
Next x

End Sub
'**********************************

Tim

 

[Rich]

Thanks. That is exactly what I was looking for. Well, almost. The user
wants dots like in your sample, but he wants me to connect them. That is
actually quite easy in my C# app. The difficulty is converting this C# app
into an activeX control - I am discovering. But we may be able to live with
just the dots.

Many thanks.

 

[Rich]

I found this sample on msdn for adding lines

Set sht = ActiveSheet
With sht.Shapes.AddLine(10, 10, 250, 250).Line
'.DashStyle = msoLineDashDotDot
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(50, 0, 128)
End With

Your sample was a great starting point. And now I can connect the dots too.

It is just unbelievable what Excel can do.

 

[Tim Williams]

Yep - pretty cool...

Tim

I found this sample on msdn for adding lines

Set sht = ActiveSheet
With sht.Shapes.AddLine(10, 10, 250, 250).Line
'.DashStyle = msoLineDashDotDot
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(50, 0, 128)
End With

Your sample was a great starting point. And now I can connect the dots
too.

It is just unbelievable what Excel can do.