Drawing a Circle if a different cell has data

[Steve R.]

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

 

[Susan]

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

 

[Steve R.]

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.

 

[Peter T]

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

 

[Peter T]

PS
to toggle ALL circle borders on/off

ActiveSheet.Ovals.LineStyle = xlAutomatic ' xlNone

Regards,
Peter T

 

[Steve R.]

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

 

[Peter T]

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

 

[Ron Rosenfeld]

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

 

[Steve R.]

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

 

[Steve R.]

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