Legnth of text in textbox control
 

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 - if
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 the
wks because some of us use macs and the control toolbox is not
supported on the mac. Alternatively if anyone has a way of putting a
scrolling textbox on the wks without using the control toolbox - or
windows ole that would be great.

Thanks
Richard


--
rgarber50
------------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350
View this thread: http://www.excelforum.com/showthread...hreadid=387196

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?

rgarber50 wrote:

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 - if
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 the
wks because some of us use macs and the control toolbox is not
supported on the mac. Alternatively if anyone has a way of putting a
scrolling textbox on the wks without using the control toolbox - or
windows ole that would be great.

Thanks
Richard

--
rgarber50
------------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350
View this thread: http://www.excelforum.com/showthread...hreadid=387196

--

Dave Peterson

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


--
rgarber50
------------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350
View this thread: http://www.excelforum.com/showthread...hreadid=387196

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").Te xt

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").va lue = 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.

rgarber50 wrote:

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

--
rgarber50
------------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350
View this thread: http://www.excelforum.com/showthread...hreadid=387196

--

Dave Peterson