Cell Locations On Sheet (measurements)

[Robert Christie]

Hi everyone

Could someone explain cell location in relation to top of
row 1 and left hand side of column A?.
If I run this macro it places a button on a worksheet
that covers the area of cell B2 on my xl2002.

ActiveSheet.Buttons.Add(48, 12.75, 48, 12.75).Select

It appears the top left hand corner of the button is the
starting point for positioning the button and therefore
the cell as well.

A column width is shown as 8.43 (64 pixels) if you
multiply 64 x 0.75 you get 48, the same as the 48 in the
code above. Also row height is shown as 12.75 (17 pixels)
if you multiply 17 x 0.75 you get 12.75 again the same as
in the code.

Questions:
1. What do the macro numbers represent? (points)
2. Is it possible to highlight a cell and run a macro or
function that gives the cells measurements in relation to
the top and side edges of a worksheet?

TIA

Regards Bob C.

 

[Jan Karel Pieterse]

Hi Robert,

Could someone explain cell location in relation to top of
row 1 and left hand side of column A?.
If I run this macro it places a button on a worksheet
that covers the area of cell B2 on my xl2002.

ActiveSheet.Buttons.Add(48, 12.75, 48, 12.75).Select

It appears the top left hand corner of the button is the
starting point for positioning the button and therefore
the cell as well.

Since you got the numbers right I guess the assumption should also be
correct.

If you're trying to place a button on top of a cell, this code does it:

Sub test()
Dim dWidth As Double
Dim dHeight As Double
Dim dTop As Double
Dim dLeft As Double
With ActiveCell
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
End With
ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Robert Christie]

Hi Jan
Thankyou for your post and code.
How do I modify your code to list dimensions in a message
box?

TIA

Regards Bob C.

 

[Jan Karel Pieterse]

Hi Robert,

How do I modify your code to list dimensions in a message
box?

Sub test()
Dim dWidth As Double
Dim dHeight As Double
Dim dTop As Double
Dim dLeft As Double
With ActiveCell
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
End With
MsgBox "Top : " & dTop & _
vbNewLine & "Left : " & dLeft & _
vbNewLine & "Width : " & dWidth & _
vbNewLine & "Height : " & dHeight
ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Robert Christie]

Thankyou very much Jan.

Bob C.

-----Original Message-----
Hi Robert,


Sub test()
Dim dWidth As Double
Dim dHeight As Double
Dim dTop As Double
Dim dLeft As Double
With ActiveCell
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
End With
MsgBox "Top : " & dTop & _
vbNewLine & "Left : " & dLeft & _
vbNewLine & "Width : " & dWidth & _
vbNewLine & "Height : " & dHeight
ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

.