Position of drawing object on Excel-worksheet
Hello,
how can I programmatically get (or set) a worksheet cell-address under a drawing object ? Regards, H.G. Lamy |
Position of drawing object on Excel-worksheet
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 |
Position of drawing object on Excel-worksheet
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 |
Position of drawing object on Excel-worksheet
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 |
Position of drawing object on Excel-worksheet
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 |
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 |
All times are GMT +1. The time now is 11:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com