Excel fetch the cell value from textbox

Question

I would like to link my excel cell with textbox. I want the textbox content to be displayed in the excel cell. I am going to link more than 1 cell to the textbox.

My situation initially looks like shown in the image below:

I tried simply in Excel, by writing: ="TextBox 9", but it didn't work.

I've read that it might be possible without using VBA code:

https://www.reddit.com/r/excel/comments/25djwo/referencing_a_text_box_in_a_cell_formula_without/

but after the right-click, i have no "Properties" options here.

On the other hand, the alternative sources say, that there is no option to do it straight away in Excel.

There are some VBA solutions available:

Pass UserForm textbox value to cell

Get the value of a textbox to a cell

but they didn't work in my case

I found the closest solution here:

https://www.extendoffice.com/documents/excel/4047-excel-convert-textbox-to-cell.html

but unfortunately, it applies to all the textboxes available in the workbook, which I don't want.

I tried to specify only one textbox, which I need:

 Sub TextboxesToCell()
 'UpdatebyExtendoffice20160918
  Dim xRg As Range, Trg As Range
  Dim xRow As Long
  Dim xCol As Long
  Dim xTxtBox As TextBox
 
  Set xRg = Application.InputBox("Select a cell):", "Copying textbox data", _
                                ActiveWindow.RangeSelection.AddressLocal, , , , , 8)
  xRow = xRg.Row
  xCol = xRg.Column

  Set xTxtBox = ThisWorkbook.Worksheets("Internal Checklist").Shapes("TextBox 9")
 
  For Each xTxtBox In ActiveSheet.TextBoxes
    Cells(xRow, xCol).Value = xTxtBox.Text
    xTxtBox.Delete
    xRow = xRow + 1
 Next
 
 End Sub

But unfortunately, I am getting an error: Type mismatch

for the line, where I defined the proper textbox

    Set xTxtBox = ThisWorkbook.Worksheets("Internal Checklist").Shapes("TextBox 9")

How can I get the data from textbox to my cell concerned?

user_7858451 · Answer

I think some confusion has occurred between the type of text box. There are three main types in excel:

Form text boxes
ActiveX text boxes
Text box shapes

You can find a bit more about the bottom two here: https://support.microsoft.com/en-ie/office/add-a-label-or-text-box-to-a-worksheet-6905d5da-fad4-432e-87ea-001f3ecd6cd6

I suspect you are using the latter, but have been looking up code to work, which would work with the the first. It probably also explains why you don't get the properties for it.

In VBA the TextBox object only works with the ActiveX text boxes. In your case when you declare xTxtBox As TextBox, you then try to set that variable to the textbox shape, which is why you get the type mismatch error.

A way round this would be to set it to a shape instead:

Dim xTxtBox As Shape

You can then replace everything in you for each loop with just the one line (unless you do want to delete the shape once you're done? I'm a bit confused with you intentions there):

Cells(xRow, xCol).Value = xTxtBox.TextFrame2.TextRange.Text

Notice the extra properties TextFrame2.TextRange. Because its a shape, you have to dig into the conntaining object to find those properties.

Or for simplicity, if you just want to set the cellvalue to what is in the text box, you can skip the declaration of the variable, and assigning it to a shape and do it in one line:

Cells(xRow, xCol).Value = ThisWorkbook.Worksheets("Internal Checklist").Shapes("TextBox 9").TextFrame2.TextRange.Text

Excel fetch the cell value from textbox

1 Answers