获取文本框的值到单元格 [英] Get the value of a textbox to a cell

问题描述

我有一个名为 TextBox 1 的文本框，其中包含段落。我用一个名字（ Insert> Name> Define ... ）定义它，名为 profile_reference 。

I have a textbox called TextBox 1 which contains paragraphs. I defined it with a name (Insert > Name > Define...) called profile_reference.

在单元格中，我输入了公式 = profile_reference ，但是我得到的值 TextBox 1 。我想做的是获取该文本框的实际值。这是可能的吗？

On a cell, I inputted the formula =profile_reference but I'm getting the value TextBox 1. What I wanted to do is to get the actual value of that textbox. Is that possible?

我正在使用PHP解析器从这个excel文件获取值，它不能获取文本框的值，它只能获取单元格值。所以我试图将该文本框的值复制到单元格中，然后解析它。

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.

推荐答案

您可以检索文本框，但这不仅仅需要标准的Excel用户界面。

You can retrieve the contents of the textbox but this will require more than just the standard Excel user interface.

文本框是一个Shape对象，是Worksheet的Shapes集合的成员。当您将文本框插入到工作表中时，Excel会给它一个名称，例如TextBox 1。因此，您可以参考VBA中包含文本框的Shape对象（例如）

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)

Worksheets("Sheet1").Shapes("TextBox 1")

因为Shape对象可以包含各种不同的东西图片或自动图形）还有一些对象，属性和方法涉及检索文本。所需的完整短语是

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

Worksheets("Sheet1").Shapes("TextBox 1").TextFrame.Characters.Text

将文本框的内容作为字符串传递。

which delivers the contents of the textbox as a String.

但是，这需要您使用Excel指定的文本框的名称（即TextBox 1）。您添加了一个名为profile_reference的名称，它引用了TextBox 1，那么如何使用您的名称而不是Excel创建的文本框来获取文本框的内容？

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?

名称是Name对象，它是Workbook的Names集合的一部分。所以你可以参考你的具体的Name对象（例如）

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)

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

Name对象的Value属性是Excel公式语法中的字符串，因此在您的情况下，此字符串为

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

'="TextBox 1"'

ie单引号内的位。这几乎是不是你想要的'TextBox 1'字符串。因此，使用您的姓名获取文本框的内容需要一点VBA，例如：

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:

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.

这篇关于获取文本框的值到单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！