Legnth of text in textbox control

R

rgarber50

I would like to create a macro that:
creates a textbox
fills the textbox with the text in "A1".
Allows the user to add or edit text in the textbox
Then when exiting the textbox:
copy the text from the textbox back to "A1"

the following macro seems to work ok - except for one problem - i
there is a lot of text in "A1" it won't copy it all into the textbox.

Private Sub UserForm_Initialize()
Dim testtext
WksText = Range("A1").Text

TextBox1.Text = WksText

TextBox1.AutoSize = False

TextBox1.WordWrap = True

TextBox1.ScrollBars = 2

TextBox1.MultiLine = True

End Sub

Private Sub UserForm_Terminate()

Range("A1") = TextBox1.Text
End Sub

Anybody have any ideas. I don't want to use a scrolling textbox in th
wks because some of us use macs and the control toolbox is no
supported on the mac. Alternatively if anyone has a way of putting
scrolling textbox on the wks without using the control toolbox - o
windows ole that would be great.

Thanks
Richar
 
D

Dave Peterson

I put =rept("asdf ",80)&"x" in A1 (401 characters in A1).

I created a small userform with 2 buttons (ok and cancel) and a textbox in it.
This was the code behind that userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Range("A1").Value = TextBox1.Text
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wksText As String

wksText = Range("A1").Text

With TextBox1
.Text = wksText
.AutoSize = False
.WordWrap = True
.ScrollBars = 2
.MultiLine = True
End With
End Sub

And when I was done, A1 had the correct number of characters in it.

========

But maybe looping through the textbox 250 characters at a time would help:

Private Sub CommandButton2_Click()
Dim myStr As String
Dim iCtr As Long

For iCtr = 1 To Len(Me.TextBox1) Step 250
myStr = myStr & Mid(Me.TextBox1.Text, iCtr, 250)
Next iCtr

Range("A1").Value = myStr
Unload Me

End Sub

======
What version of excel are you using?
 
R

rgarber50

Dave

I tried your macro - it is not reading all the text in "A1" into the
textbox. I am taking your idea of writing 250 characters at a time into
"A1" [i think i am understanding your command button 2 procedure ] and
am playing with worksheet functions - mid and len to try and read into
the textbox 250 characters at a time. What do you think?

I am wondering if the problem may be that I am working on a mac -
latest version of excel for osx.

Thanks for your help.

Richard
 
D

Dave Peterson

This portion:

Private Sub UserForm_Initialize()
Dim wksText As String

wksText = Range("A1").Text

With TextBox1
.Text = wksText
.AutoSize = False
.WordWrap = True
.ScrollBars = 2
.MultiLine = True
End With
End Sub

Reads A1 in the activesheet. If the wrong sheet is active, it would be better
to fully qualify the range.

Private Sub UserForm_Initialize()
Dim wksText As String

wksText = activeworkbook.worksheets("Sheet1").Range("A1").Text

With TextBox1
.Text = wksText
.AutoSize = False
.WordWrap = True
.ScrollBars = 2
.MultiLine = True
End With
End Sub

Same when you write it out:

Range("A1").Value = myStr
becomes
activeworkbook.worksheets("Sheet1").Range("A1").value = myStr

========
And I know nothing about Macs. Are you sure that your version of excel supports
more than 255 characters. (xl95 only allowed 255.)

Maybe posting to:
microsoft.public.mac.office.excel
would lead to a better answer.
Dave

I tried your macro - it is not reading all the text in "A1" into the
textbox. I am taking your idea of writing 250 characters at a time into
"A1" [i think i am understanding your command button 2 procedure ] and
am playing with worksheet functions - mid and len to try and read into
the textbox 250 characters at a time. What do you think?

I am wondering if the problem may be that I am working on a mac -
latest version of excel for osx.

Thanks for your help.

Richard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top