Forum Discussion

plotnik25's avatar
plotnik25
Copper Contributor
Nov 27, 2019

Excel coordinats of cell midpoint

Hello, I would like to know, if it is possible to get the coordinates (x/y) of a certain cell in Excel, preferable using vba. Background: I want to center a form around the cell. Thank you for your ...
excel
Macros and VBA
Russell Proctor's avatar
Russell Proctor
Brass Contributor
Nov 27, 2019
Hi,
It is possible to get the 'Top' and 'Left' position (in points) for individual cells and ranges.
These positions can then be used for positioning.

 

 

Sub AddShape()
Dim wsh As Excel.Worksheet
   Set wsh = ActiveSheet
   Call wsh.Shapes.AddShape(Type:=MsoAutoShapeType.msoShapePentagon, _
                            Left:=Range("B2").Left, _
                            Top:=Range("B2").Top, _
                            Width:=120, _
                            Height:=120).Select
End Sub

 

 

You could use these 'Top' and 'Left' values to help position a userform over your worksheet.

 

 

Private Sub UserForm_Initialize()
   'assuming you changed the StartUpPosition property to 'Manual'
   'assuming the default font and assuming the ribbon tab is displayed
   Me.Left = Range("C3").Left + 15
   Me.Top = Range("C3").Top + 170
End Sub

 

 

Hope this helps
Russell

Resources