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

 

[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