excel - Get the value of a textbox to a cell

Question

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

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?

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.

Answer 1

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

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")

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.

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?

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

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"'

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.