vba 获取文本框的值到单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21673105/
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
Get the value of a textbox to a cell
提问by kevinandrada
I have a textbox called TextBox 1which contains paragraphs. I defined it with a name (Insert > Name > Define...) called profile_reference.
我有一个名为的文本框TextBox 1，其中包含段落。我用一个名为 ( Insert > Name > Define...)的名称定义了它profile_reference。
On a cell, I inputted the formula =profile_referencebut I'm getting the value TextBox 1. What I wanted to do is to get the actual value of that textbox. Is that possible?
在一个单元格上，我输入了公式，=profile_reference但我得到了值TextBox 1。我想要做的是获取该文本框的实际值。那可能吗？
I'm using a PHP parser to get values from this excel file and it can not get the value for textboxes, it can only get cell values. So I'm trying to copy the value of that textbox to a cell then parse it.
我正在使用 PHP 解析器从这个 excel 文件中获取值，但它无法获取文本框的值，它只能获取单元格值。所以我试图将该文本框的值复制到一个单元格然后解析它。
回答by DMM
You can retrieve the contents of the textbox but this will require more than just the standard Excel user interface.
您可以检索文本框的内容，但这需要的不仅仅是标准 Excel 用户界面。
A textbox is a Shape object, and a member of the Worksheet's Shapes collection. When you insert a textbox into a worksheet, Excel gives it a name such as "TextBox 1". Therefore, you can refer to the Shape object containing a textbox in VBA as (for example)
文本框是一个 Shape 对象，并且是 Worksheet 的 Shapes 集合的成员。将文本框插入工作表时，Excel 会为其命名，例如“TextBox 1”。因此，您可以在 VBA 中将包含文本框的 Shape 对象称为（例如）
Worksheets("Sheet1").Shapes("TextBox 1")
Because the Shape object can contain a variety of different things (such as pictures or AutoShapes) a few more objects, properties and methods are involved in retrieving the text. The full phrase required is
因为 Shape 对象可以包含各种不同的东西（例如图片或自选图形），所以在检索文本时涉及更多的对象、属性和方法。所需的完整短语是
Worksheets("Sheet1").Shapes("TextBox 1").TextFrame.Characters.Text
which delivers the contents of the textbox as a String.
它将文本框的内容作为字符串传递。
However, this requires you to use the name of the textbox specified by Excel (i.e. "TextBox 1"). You have added a a name called "profile_reference" which refers to TextBox 1, so how do you get the contents of the textbox using your name rather than the one created by Excel?
但是，这要求您使用 Excel 指定的文本框的名称（即“TextBox 1”）。您添加了一个名为“profile_reference”的名称，它指的是 TextBox 1，那么如何使用您的名称而不是 Excel 创建的名称获取文本框的内容？
A name is a Name object which is part of the Workbook's Names collection. So you can refer to your specific Name object as (for example)
名称是一个 Name 对象，它是 Workbook 的 Names 集合的一部分。因此，您可以将特定的 Name 对象称为（例如）
ActiveWorkbook.Names("profile_reference")
and the "refers to" part of the object (the bit you specify using the user interface) is
并且对象的“引用”部分（您使用用户界面指定的位）是
ActiveWorkbook.Names("profile_reference").Value
The Value property of a Name object is a character string in the syntax of Excel's formulae so in your case this string is
Name 对象的 Value 属性是 Excel 公式语法中的字符串，因此在您的情况下，此字符串是
'="TextBox 1"'
i.e. the bit inside the single quotes. This is almost but not quite the 'TextBox 1' string that you want. Therefore, getting at the contents of the textbox using your name requires a little bit of VBA, such as:
即单引号内的位。这几乎但不完全是您想要的“TextBox 1”字符串。因此，使用您的名字获取文本框的内容需要一点 VBA，例如：
Dim strTB As String
strTB = ActiveWorkbook.Names("profile_reference").Value
strTB = Mid(strTB, 3, Len(strTB) - 3) 'Strips unwanted =" and " chars from start/end
MsgBox Worksheets("Sheet1").Shapes(strTB).TextFrame.Characters.Text
I've used a message box to show the contents of the textbox. The contents could equally be assigned to a worksheet cell. You could, if you wanted, wrap this up in a short user-defined function (which perhaps takes your name as its input argument) providing you with a convenient mechanism for placing the contents of the textbox into a worksheet cell.
我使用了一个消息框来显示文本框的内容。内容可以同样分配给工作表单元格。如果需要，您可以将其封装在一个简短的用户定义函数中（它可能将您的姓名作为其输入参数），为您提供一种将文本框的内容放入工作表单元格的便捷机制。
