Creating a circle/ellipse at a specific cell (as pictured inside)

**vbaDunce** · 09-08-2014, 10:16 AM

Hello everyone,

I'm working on a thermometer-style goalpost, and it's pretty much done, however, I need to know how to make it look like this at the end:

therm.JPG

Based on the number of monthly tasks, my macro creates the thermometer but I'm having trouble figuring out how to attach the bulb at this precise location when the variable-height thermometer is finished drawing. I want it to land pretty much at the bottom of Task 1's cell.

Any help would be appreciated, thank you!

**MrShorty** · 09-08-2014, 10:33 AM

I'm not exactly sure how you are creating the thermometer chart. To add the bulb, I would suggest you follow Andy Pope's outline here: http://andypope.info/charts/thermometer.htm

**vbaDunce** · 09-08-2014, 10:39 AM

Originally Posted by MrShorty

I'm not exactly sure how you are creating the thermometer chart. To add the bulb, I would suggest you follow Andy Pope's outline here: http://andypope.info/charts/thermometer.htm

I'm interning at a company, and I have to follow the exact style they are already creating manually, though those that you linked are pretty damn cool.

EDIT: It's not a chart, it's a series of bordered and numbered cells, with formatting based on whether or not the task number represents 80% for the week.

**MrShorty** · 09-08-2014, 11:09 AM

I think the reason I suggested Andy's examples is because it would be easier to put a circle at the bottom of a chart than at the bottom of a block of cells.

Does the "bulb" of your thermometer have to be a circle? Since cells are rectangles, it seems to me that it would be pretty easy to put a rectangular bulb at the bottom of the "chart" when made in the spreadsheet like this. You could use the cells around the bottom cell to color a larger "block" than the column of the thermometer.

If it must be a circle/ellipse, I expect you have to use some kind of drawing object/text box combination. I never really use drawing objects like this in Excel, so it seems like this would be the most difficult approach.

**vbaDunce** · 09-08-2014, 11:15 AM

I totally agree that making it a rectangle or square would be far more convenient and easier to implement.

Unfortunately, it's gotta be a circle. I've figured how to actually draw a circle with vba, working on its positioning based on the very last cell drawn in my loop structure.

**MrShorty** · 09-08-2014, 11:46 AM

At this point, you probably know more than I do about drawing and placing circles in a spreadsheet than I do, then.

**Andy Pope** · 09-08-2014, 12:03 PM

something like the following

Sub AddBuld()

    Dim ToCell As Range
    Dim Radius As Single
    Dim Bulb As Shape
    
    Set ToCell = Range("F16")
    Radius = 30
    Set Bulb = ActiveSheet.Shapes.AddShape(msoShapeOval, 1, 1, Radius * 2, Radius * 2)
    With Bulb
        .Left = ToCell.Left + ((ToCell.Width - .Width) / 2)
        .Top = ToCell.Top + ToCell.Height
        .Fill.ForeColor.RGB = vbRed
        .Line.ForeColor.RGB = vbRed
    End With
End Sub