Drawing a Circle if a different cell has data
 

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

Drawing a Circle if a different cell has data
 

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. 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

Drawing a Circle if a different cell has data
 

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. 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

Drawing a Circle if a different cell has data
 

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." wrote in message
...
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

Drawing a Circle if a different cell has data
 

PS
to toggle ALL circle borders on/off

ActiveSheet.Ovals.LineStyle = xlAutomatic ' xlNone

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
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." wrote in message
...
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

Drawing a Circle if a different cell has data
 

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

Drawing a Circle if a different cell has data
 

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." wrote in message
...

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

Drawing a Circle if a different cell has data
 

On Fri, 29 Jun 2007 12:00:00 -0700, Steve R.
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

Drawing a Circle if a different cell has data
 

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

Drawing a Circle if a different cell has data
 

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