Solved

EXCEL / VBA - Text Boxes and rectangles

Posted on 2002-04-17
2
272 Views
Last Modified: 2008-03-10
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
Comment
Question by:toffee
2 Comments
 
LVL 22

Accepted Solution

by:
ture earned 100 total points
ID: 6948139
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
 

Author Comment

by:toffee
ID: 6948193
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now