EXCEL / VBA - Text Boxes and rectangles

I'm after a bit of VBA to do the following:-

I need to extract text which has been entered into a Text Box on an Excel sheet and write it into a cell.

Also on the same sheet are rectangles in which text has been written.  I also need to extract this and write it to a cell.

The reason for this is a badly designed spreadsheet (not mine)which was mailed to hundreds of people for them to fill in some comments.  I now have been asked to collate the results.

Many thanks in advance...
toffeeAsked:
Who is Participating?
 
tureConnect With a Mentor Commented:
toffee,

The approach depends on what you mean by a "text box". Excel calls shapes (rectangles) with text for "text boxes" but also the textboxes that are usually put on forms to edit text fields.

The VBA procedure below shows you how to handle both types of textboxes.

Sub ReadFromTextBox()
  Dim tb As msforms.TextBox
  Dim sh As Shape
 
  Set tb = ActiveSheet.TextBox1
  Range("A1").Value = tb.Text
 
  Set sh = ActiveSheet.Shapes("Text Box 3")
  Range("A2").Value = sh.TextFrame.Characters.Text
End Sub

Ture Magnusson
Karlstad, Sweden
0
 
toffeeAuthor Commented:
Excellent!!!

This is the bit which worked.  Many thanks.

Dim sh As Shape
Set sh = ActiveSheet.Shapes("Text Box 3")
Range("A2").Value = sh.TextFrame.Characters.Text

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.