• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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...
0
toffee
Asked:
toffee
1 Solution
 
tureCommented:
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now