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

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


"Rich" <(E-Mail Removed)> wrote in message
news:1197E65C-CDA1-4EDA-A405-(E-Mail Removed)...
>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

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

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

--
Rick (MVP - Excel)


"Rich" <(E-Mail Removed)> wrote in message
news:1197E65C-CDA1-4EDA-A405-(E-Mail Removed)...
>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

With a percentage in A1

Enter this in B1

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


Gord Dibben MS Excel MVP



On Fri, 20 Nov 2009 10:59:03 -0800, Rich <(E-Mail Removed)>
wrote:

>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

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.


"Gord Dibben" wrote:

> With a percentage in A1
>
> Enter this in B1
>
> =REPT(CHAR(149),A1*CELL("width",B1))
>
>
> Gord Dibben MS Excel MVP
>
>
>
> On Fri, 20 Nov 2009 10:59:03 -0800, Rich <(E-Mail Removed)>
> wrote:
>
> >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
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:8A0DC3DA-95D2-40EF-9093-(E-Mail Removed)...
> 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.
>
>
> "Gord Dibben" wrote:
>
>> With a percentage in A1
>>
>> Enter this in B1
>>
>> =REPT(CHAR(149),A1*CELL("width",B1))
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>>
>> On Fri, 20 Nov 2009 10:59:03 -0800, Rich <(E-Mail Removed)>
>> wrote:
>>
>> >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
>>
>> .
>>

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.



"Tim Williams" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:8A0DC3DA-95D2-40EF-9093-(E-Mail Removed)...
> > 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.
> >
> >
> > "Gord Dibben" wrote:
> >
> >> With a percentage in A1
> >>
> >> Enter this in B1
> >>
> >> =REPT(CHAR(149),A1*CELL("width",B1))
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >>
> >>
> >> On Fri, 20 Nov 2009 10:59:03 -0800, Rich <(E-Mail Removed)>
> >> wrote:
> >>
> >> >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
> >>
> >> .
> >>
>
>
> .
>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:8A0DC3DA-95D2-40EF-9093-(E-Mail Removed)...
> > 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.
> >
> >
> > "Gord Dibben" wrote:
> >
> >> With a percentage in A1
> >>
> >> Enter this in B1
> >>
> >> =REPT(CHAR(149),A1*CELL("width",B1))
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >>
> >>
> >> On Fri, 20 Nov 2009 10:59:03 -0800, Rich <(E-Mail Removed)>
> >> wrote:
> >>
> >> >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
> >>
> >> .
> >>
>
>
> .
>

Yep - pretty cool...

Tim

"Rich" <(E-Mail Removed)> wrote in message
news:CCA8A521-D5F4-4976-B17D-(E-Mail Removed)...
>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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:8A0DC3DA-95D2-40EF-9093-(E-Mail Removed)...
>> > 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.
>> >
>> >
>> > "Gord Dibben" wrote:
>> >
>> >> With a percentage in A1
>> >>
>> >> Enter this in B1
>> >>
>> >> =REPT(CHAR(149),A1*CELL("width",B1))
>> >>
>> >>
>> >> Gord Dibben MS Excel MVP
>> >>
>> >>
>> >>
>> >> On Fri, 20 Nov 2009 10:59:03 -0800, Rich
>> >> <(E-Mail Removed)>
>> >> wrote:
>> >>
>> >> >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
>> >>
>> >> .
>> >>
>>
>>
>> .
>>