Hello,

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

Regards,

H.G. Lamy

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.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=173782

Microsoft Office Help

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"H.G. Lamy" wrote in message
...
Hello,

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

Regards,

H.G. Lamy

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" wrote:

Hello,

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

Regards,

H.G. Lamy

--

Dave Peterson

Thank you very much !

hg

"H.G. Lamy" wrote in message
...
Hello,

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

Regards,

H.G. Lamy

Test

On Wednesday, January 27, 2010 6:21 AM H.G. Lamy wrote:

Hello,

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

Regards,

H.G. Lamy


On Wednesday, January 27, 2010 7:02 AM joel wrote:

You cannot. 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.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=173782

Microsoft Office Help


On Wednesday, January 27, 2010 7:19 AM Andy Pope wrote:

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


On Wednesday, January 27, 2010 9:24 AM Dave Peterson wrote:

I am not sure what you are doing, but here is some code that I have 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" wrote:

--

Dave Peterson


On Wednesday, January 27, 2010 9:28 AM H.G. Lamy wrote:

Thank you very much !

hg