elzell
elzell

Reputation: 2306

Excel VBA: Get cell at center of shape

I have a few shapes (or more specifically, dropdown lists) which are roughly aligned to individual cells and would like to know for each shape which cell lies underneath.

I know there are the following members:

Dim dd As Shape
dd.BottomRightCell
dd.TopLeftCell

to get the bottom right or top left cell under the shape, but since the shapes are manually placed and not always properly aligned (sometimes the bottom right corner touches the row beneath, sometimes the top left corner the row above, ...) both values can not be trusted.

I would like to know which cell I get when I look at the center of the shape. Is there a way to get this information?

Upvotes: 1

Views: 2132

Answers (1)

Preston
Preston

Reputation: 8187

You could use this to find the centre point, I'm unsure how to select a cell based on the top/left position, is this adequate?

lngLeftCentre = shape.Left + (shape.Width / 2)
lngTopCentre = shape.Top + (shape.Height / 2)
Debug.Print "Top: " & lngTopCentre
Debug.Print "Left: " & lngLeftCentre

Upvotes: 1

Related Questions