|
Posted to microsoft.public.excel.programming
|
|
|
Position of drawing object on Excel-worksheet
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
|