插入文本框并设置公式 [英] Insert a TextBox and set the formula

问题描述

在VBA for Excel 2007中，我想将文本框添加到活动工作表并将其公式设置为单元格.我的问题是AddTextbox函数返回类型名称为Shape而不是TextBox的对象，因此它没有要设置的Formula属性.相反，我最终循环浏览所有文本框以找到正确的文本框，然后设置其Formula.有更好的方法吗?

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 AddTextbox function returns an object with typename Shape, not TextBox, so it does not have a Formula property 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?

Sub insertTextBoxWithFormula()
    Set newTextBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 200, 200, 150, 150)
    newTextBox.Name = "New TextBox"
    'newTextBox.Formula = "=$A$1" '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

推荐答案

您可以使用控件的名称来索引TextBoxes集合.因此，您的示例可以缩写为:

You can index the TextBoxes collection using the name of the control. So your example can be abbreviated to:

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 200, 200, 150, 150).Name = "New TextBox"
ActiveSheet.TextBoxes("New TextBox").Formula = "=$B$3"

这篇关于插入文本框并设置公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！