PDA

View Full Version : Excel 2000 textbox help



simples
09-30-2011, 01:24 PM
Hi all. I need to take text values from various cells and then dump them to a (Forms toolbar) textbox on a worksheet.

The textbox must be a fixed width. The final text could be (examples only) ten words or 100 words. So for the latter the text would definitely run to more than one line.

Is there some code that can work out the number of lines of text so I can then adjust the textbox height to fit?

Thanks in advance. :)

Rob342
09-30-2011, 02:18 PM
Suppose you could set the height of the textbox to the value of the cell, have you tried setting property value wordwrap to true.

A sample copy of the workbook & form would help wih precise instructions, as this helps everybody looking at the post.

simples
09-30-2011, 02:37 PM
Ignore "Forms toolbar", that should've been "drawing toolbar". It's a drawing toolbar textbox ATM.

So there's no property value wordwrap, form, etc. It's not ActiveX.

Attached a workbook. Click on C8 and the pink textbox fills fine. Click on C9 and "the textbox height to fit?" text is not visible. So I need a way to figure the textbox height needed to display all the text. I can't use "automatic size" because the width mustn't exceed a preset value.

Hope this is clearer now, thanks. :)

simples
10-01-2011, 08:30 AM
After some digging around I put this together, for a Control Toolbox Text Box:

Sub test()
Dim txtforTB1 As String
Set txtBox = ActiveSheet.TextBox1

txtforTB1 = Range("B11")
txtBox.Text = txtforTB1

txtBox.Activate
txtBox.Height = txtBox.LineCount * 15
Range("A1").Select

End Sub

Text Box property settings: Autosize = False, MultiLine = True, WordWrap = True.

Seems to be working (in case anyone shows up looking for something similar).

Rob342
10-04-2011, 06:04 AM
Simples

Found this bit of code by Contextures, thought it might be useful all done with data validation
Posted a simple copy ok

Rob