Position of drawing object on Excel-worksheet

[H.G. Lamy]

Hello,

how can I programmatically get (or set) a worksheet cell-address under a
drawing object ?

Regards,

H.G. Lamy

 

[joel]

You can't. Sahpes sit ontop of the worksheet and are not in any of the
cells. The only way you can locate a cell and a shape is using the
pixel location. Both shapes and cells have the following properties:

Left, Top, width, height.

The top left corner of the screen is (0,0) coordinate axis with x axis
going positive to the right and y axis (backwards) going positive down.
You can think of the pixels as simple (x,y). The width property is the
x distance and the y distance is the height property.

If you look at the left position of column A and the left position of
column B the distance between the two isw a little larger than the width
of column A because there is a small border around each cell.

Also be aware if you cahnge the width of a column or thge height of the
row the cell will move but a shape will still stay in its original
position.

 

[Andy Pope]

Hi,

You can use the shapes .TopLeftCell and BottomRightCell properties.

msgbox Activesheet.shapes(1).topleftcell.address

The properties are read-only. To change the cell being referenced you would
need to use the shapes .left, .top, .Width and . Height properties.

Cheers
Andy

 

[Dave Peterson]

I'm not sure what you're doing, but here's some code that I've used to add
checkboxes from the Forms toolbar to a range of cells in a worksheet:

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In .Range("B3:B10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX = xlOn Then
'do something
Else
'do something else
End If

End Sub

 

[H.G. Lamy]

Thank you very much !

hg