vba在Excel中的特定单元格位置添加一个形状 [英] vba to add a shape at a specific cell location in Excel

问题描述

我正在尝试在特定的单元格位置添加一个形状，但由于某种原因不能在所需的位置添加形状。以下是我用来添加形状的代码：

I am trying to add a shape at a specific cell location but cannot get the shape added at the desired location for some reason. Below is the code I am using to add the shape:

Cells(milestonerow, enddatecellmatch.Column).Activate

Dim cellleft As Single
Dim celltop As Single
Dim cellwidth As Single
Dim cellheight As Single

cellleft = Selection.Left
celltop = Selection.Top

ActiveSheet.Shapes.AddShape(msoShapeOval, cellleft, celltop, 4, 10).Select

我使用变量来捕获左侧和顶部位置，以检查在我的代码中设置的值，而不是在录制宏时在活动位置手动添加形状时看到的值。当我运行代码时，cellleft = 414.75和celltop = 51，但是当我录制宏时手动将形状手动添加到活动单元格位置，cellleft = 318.75和celltop = 38.25。我已经解决了这个问题了一段时间，并已经在网上查看了很多现有的关于添加形状的问题，但我无法理解。任何帮助将不胜感激。

I used variables to capture the left and top positions to check the values that were being set in my code vs. the values I saw when adding the shape manually in the active location while recording a macro. When I run my code, cellleft = 414.75 and celltop = 51, but when I add the shape manually to the active cell location while recording a macro, cellleft = 318.75 and celltop = 38.25. I have been troubleshooting this for a while and have looked over a lot of existing questions online about adding shapes, but I cannot figure this out. Any help would be greatly appreciated.

推荐答案

这似乎对我有用。我在末尾添加了debug语句，以显示形状的 .Top 和 .Left 等于单元格的 .Top 和 .Left 值。

This seems to be working for me. I added the debug statements at the end to display whether the shape's .Top and .Left are equal to the cell's .Top and .Left values.

为此，我选择了单元格 C2 。

For this, I had selected cell C2.

Sub addshapetocell()

Dim clLeft As Double
Dim clTop As Double
Dim clWidth As Double
Dim clHeight As Double

Dim cl As Range
Dim shpOval As Shape

Set cl = Range(Selection.Address)  '<-- Range("C2")

clLeft = cl.Left
clTop = cl.Top
clHeight = cl.Height
clWidth = cl.Width

Set shpOval = ActiveSheet.Shapes.AddShape(msoShapeOval, clLeft, clTop, 4, 10)

Debug.Print shpOval .Left = clLeft
Debug.Print shpOval .Top = clTop

End Sub

这篇关于vba在Excel中的特定单元格位置添加一个形状的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！