?
Solved

EXCEL / VBA - Text Boxes and rectangles

Posted on 2002-04-17
2
Medium Priority
?
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
ture earned 400 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

752 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