Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set Circle Size from cell data? | Excel Discussion (Misc queries) | |||
How do I make circle within circle graphs in Excel 2003? | Charts and Charting in Excel | |||
drawing/printing an exact circle | New Users to Excel | |||
Drawing a circle on a protected sheet | Excel Discussion (Misc queries) | |||
Drawing a semi-circle in Excel | Excel Programming |