Hello,
I need to draw a circle around a Y in a cell. If a differenct cell has been
populated. I actually need to do this on several lines within the worksheet.
Am using Excel 2002. If the test cell is empty I just need the Y with no
circle.

Thanks

an idea
on another sheet, with gridlines hidden, make a "Y"
then, away from the first one, make the "Y" with a transparent circle
around it.
copy each area & paste to MSPaint, or something, as a picture file.
save. copy picture file into excel. hide that worksheet.

if xxxx <>"" then
show y picture
else
show ycircle picture
end if


susan


On Jun 29, 3:00 pm, Steve R. <Ste...@discussions.microsoft.com> wrote:
> Hello,
> I need to draw a circle around a Y in a cell. If a differenct cell has been
> populated. I actually need to do this on several lines within the worksheet.
> Am using Excel 2002. If the test cell is empty I just need the Y with no
> circle.
>
> Thanks

That might work, but was hoping for a withing spreadsheet option. I know I
can create a macro that will display the circle, but havent figured out how
to run that macro if there is data in the other cell.

"Susan" wrote:

> an idea
> on another sheet, with gridlines hidden, make a "Y"
> then, away from the first one, make the "Y" with a transparent circle
> around it.
> copy each area & paste to MSPaint, or something, as a picture file.
> save. copy picture file into excel. hide that worksheet.
>
> if xxxx <>"" then
> show y picture
> else
> show ycircle picture
> end if
>
>
> susan
>
>
> On Jun 29, 3:00 pm, Steve R. <Ste...@discussions.microsoft.com> wrote:
> > Hello,
> > I need to draw a circle around a Y in a cell. If a differenct cell has been
> > populated. I actually need to do this on several lines within the worksheet.
> > Am using Excel 2002. If the test cell is empty I just need the Y with no
> > circle.
> >
> > Thanks
>
>
>

Sub test()
Dim shp As Shape
Dim rng As Range

'run multiple times to test toggled value

' say the condition cell is right of the Y cell
' and the condition =1

Set rng = Range("B3") '>> Change

With rng.Offset(0, 1)
.Value = IIf(.Value = 1, 0, 1) ' toggle value
End With

On Error Resume Next
Set shp = ActiveSheet.Shapes("Yes" & rng.Address(0, 0))
If shp Is Nothing Then
Set shp = ActiveSheet.Shapes.AddShape(msoShapeOval, 1, 1, 1, 1)
With shp
.Name = "Yes" & rng.Address(0, 0)
.Line.Weight = 0.75
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
End With
End If
On Error GoTo 0

With rng
shp.Left = .Left + .Width / 2 - .Height / 2
shp.Top = .Top
shp.Width = .Height
shp.Height = .Height
shp.Fill.Visible = msoFalse
shp.Line.Visible = .Offset(0, 1) = 1
.Value = "Y"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

End Sub

Regards,
Peter T



"Steve R." <(E-Mail Removed)> wrote in message
news2CC1218-0B65-4486-810A-(E-Mail Removed)...
> Hello,
> I need to draw a circle around a Y in a cell. If a differenct cell has
been
> populated. I actually need to do this on several lines within the
worksheet.
> Am using Excel 2002. If the test cell is empty I just need the Y with no
> circle.
>
> Thanks

PS
to toggle ALL circle borders on/off

ActiveSheet.Ovals.LineStyle = xlAutomatic ' xlNone

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> Sub test()
> Dim shp As Shape
> Dim rng As Range
>
> 'run multiple times to test toggled value
>
> ' say the condition cell is right of the Y cell
> ' and the condition =1
>
> Set rng = Range("B3") '>> Change
>
> With rng.Offset(0, 1)
> .Value = IIf(.Value = 1, 0, 1) ' toggle value
> End With
>
> On Error Resume Next
> Set shp = ActiveSheet.Shapes("Yes" & rng.Address(0, 0))
> If shp Is Nothing Then
> Set shp = ActiveSheet.Shapes.AddShape(msoShapeOval, 1, 1, 1, 1)
> With shp
> .Name = "Yes" & rng.Address(0, 0)
> .Line.Weight = 0.75
> .Line.DashStyle = msoLineSolid
> .Line.Style = msoLineSingle
> End With
> End If
> On Error GoTo 0
>
> With rng
> shp.Left = .Left + .Width / 2 - .Height / 2
> shp.Top = .Top
> shp.Width = .Height
> shp.Height = .Height
> shp.Fill.Visible = msoFalse
> shp.Line.Visible = .Offset(0, 1) = 1
> .Value = "Y"
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlCenter
> End With
>
> End Sub
>
> Regards,
> Peter T
>
>
>
> "Steve R." <(E-Mail Removed)> wrote in message
> news2CC1218-0B65-4486-810A-(E-Mail Removed)...
> > Hello,
> > I need to draw a circle around a Y in a cell. If a differenct cell has
> been
> > populated. I actually need to do this on several lines within the
> worksheet.
> > Am using Excel 2002. If the test cell is empty I just need the Y with no
> > circle.
> >
> > Thanks
>
>

Peter
This is close to what I need, have to expand on my original post though.
This will be for a range of cells G25 - G32 and the cell I am testing is A25
- A32 How would that change the code you posted?

Thanks

Sub test2()
Dim bCondition As Boolean
Dim n As Long
Dim rngY As Range
Dim rngC, cel As Range
Dim shp As Shape

Set rngC = Range("A25:A32") ' test cells
Set rngY = Range("G25:G32") 'Y circle cells

rngC.Value = 0

n = 0
bCondition = False
For Each cel In rngY
n = n + 1
On Error Resume Next
Set shp = Nothing
Set shp = ActiveSheet.Shapes("Yes" & cel.Address(0, 0))

If shp Is Nothing Then
Set shp = ActiveSheet.Shapes.AddShape(msoShapeOval, 1, 1, 1, 1)
On Error GoTo 0
With shp
.Name = "Yes" & cel.Address(0, 0)
.Line.Weight = 0.75
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
End With
End If
On Error GoTo 0

bCondition = rngC(n, 1) = 1 ' test is true if the test cell = 1 but change
With rngY(n, 1)
shp.Left = .Left + .Width / 2 - .Height / 2
shp.Top = .Top
shp.Width = .Height
shp.Height = .Height
shp.Fill.Visible = msoFalse
shp.Line.Visible = bCondition

.Value = "Y"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Next
End Sub

Regards,
Peter T

"Steve R." <(E-Mail Removed)> wrote in message
news:CFF3E404-28E4-427D-97D3-(E-Mail Removed)...
>
> Peter
> This is close to what I need, have to expand on my original post though.
> This will be for a range of cells G25 - G32 and the cell I am testing is
A25
> - A32 How would that change the code you posted?
>
> Thanks

On Fri, 29 Jun 2007 12:00:00 -0700, Steve R. <(E-Mail Removed)>
wrote:

>Hello,
>I need to draw a circle around a Y in a cell. If a differenct cell has been
>populated. I actually need to do this on several lines within the worksheet.
>Am using Excel 2002. If the test cell is empty I just need the Y with no
>circle.
>
>Thanks

There is a Windows supplied font editor that you could use to create such a
character. Then use a simple IF statement:

e.g.

A1: =IF(LEN(B1)=0,"Y","<your_special_character>")


There is the Private Character Editor supplied with Windows.
You can create custom characters with it and make them part
of one or more fonts.
In "Run" enter: eudcedit.exe
You will have to read the help file.
--ron

Peter
That did the trick, I did need to use it on different sheets, with different
locations, but I managed to figure that part out. Thanks for your help

Steve

Ron
Thanks for this info. Won't do me much good in this instance as the program
for this will be run on several systems. But is good to know

Steve