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

vbaDunce

New Member
Joined
Sep 8, 2014
Messages
3
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:

343885d1410185639-creating-a-circle-ellipse-at-a-specific-cell-as-pictured-inside-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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
Code:
Sub Oval1_Click()
ActiveSheet.Shapes("Oval 1").Top = 50 ' or Range("A1").value
End Sub
 
Last edited:
Upvote 0
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. :)
 
Last edited:
Upvote 0
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"
Code:
ActiveSheet.Shapes("Oval 1").Top = Range("D10").Top + Range("D10").Height
 
Upvote 0
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"
Code:
ActiveSheet.Shapes("Oval 1").Top = Range("D10").Top + Range("D10").Height


That did! Thanks so much
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top