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

[vbaDunce]

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:

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!

 

[MickG]

I imagine your bulb is an Oval from the "Shapes" menu
If you find its name, you can move the "Bulb" up and down by simple code as below.
Just change the Name and the 50 as required !!!
If you right click the shape and choose "Assign Macro", "New", then a code window like below will show and you can paste your code.
When you then click your shape it should move up or down as per you Number.
You can change the number by changing the "50" to a Range value like Range("A1").value

Sub Oval1_Click()
ActiveSheet.Shapes("Oval 1").Top = 50 ' or Range("A1").value
End Sub

 

[vbaDunce]

This looks like it'll work!

I have it moving up and down, however, it seems like the input value of its relative position on the entire sheet, right? Is there a way to make it have its relative position based on a specific cell (for example, my very last drawn box that represents my Task 1)?

EDIT: I'm googling to see how to get a cell's XY position.

 

[MickG]

If you task column was "D" and Task 1 was "D10" then you could use the below to set the top of the circle at the bottom of cell "D10"

ActiveSheet.Shapes("Oval 1").Top = Range("D10").Top + Range("D10").Height

 

[vbaDunce]

If you task column was "D" and Task 1 was "D10" then you could use the below to set the top of the circle at the bottom of cell "D10"

ActiveSheet.Shapes("Oval 1").Top = Range("D10").Top + Range("D10").Height

That did! Thanks so much

 

[MickG]

You're welcome