vba 插入一个TextBox并设置公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23772309/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert a TextBox and set the formula
提问by waternova
In VBA for Excel 2007, I want to add a textbox to the active sheet and set its formula to a cell. My problem is that the AddTextboxfunction returns an object with typename Shape, not TextBox, so it does not have a Formulaproperty to set. Instead, I ended up looping through all the textboxes to find the right one, then set its Formula. Is there a better way to do this?
在 Excel 2007 的 VBA 中，我想向活动工作表添加一个文本框并将其公式设置为一个单元格。我的问题是该AddTextbox函数返回一个带有 typename 的对象Shape，而不是TextBox，因此它没有Formula要设置的属性。相反，我最终遍历所有文本框以找到正确的文本框，然后将其设置为Formula. 有一个更好的方法吗？
Sub insertTextBoxWithFormula()
Set newTextBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 200, 200, 150, 150)
newTextBox.Name = "New TextBox"
'newTextBox.Formula = "=$A" 'This is what I wanted to do
'This is what I did instead
For Each CurrentTextBox In ActiveSheet.TextBoxes
If CurrentTextBox.Name = "New TextBox" Then
CurrentTextBox.Formula = "=B3"
CurrentTextBox.Name = "Finished TextBox"
End If
Next CurrentTextBox
End Sub
回答by Joe Farrell
You can index the TextBoxes collection using the name of the control. So your example can be abbreviated to:
您可以使用控件名称索引 TextBoxes 集合。所以你的例子可以缩写为：
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 200, 200, 150, 150).Name = "New TextBox"
ActiveSheet.TextBoxes("New TextBox").Formula = "=$B"
回答by L42
Or this:
或这个：
Dim newshp As Shape
Dim newtb As TextBox
Set newshp = ActiveSheet.Shapes.AddTextbox _
(msoTextOrientationHorizontal, 200, 200, 150, 150)
Set newtb = newshp.OLEFormat.Object
newtb.Formula = "$A"
