How to overcome Inputbox 255-char limit?

[John Svendsen]

Hi All:

I'd sure appreciate some help in finding a way to replace VBA's Inputbox
because of its 255-char limit

While running existing VBA code in Excel 2003, I display text on the screen
using Inputbox, I need to do some small edits to the text and then press
OKm then the VBA code takes this corrected text and goes on.

The Problem is that Inputbox does not deal with long text, I sometimes need
to display and edit 1000-char text.
I have tried, unsuccessfully, to use a VBA textform (over my head I guess).

Can some one please be so kind as to give me a clue as to how to solve my
problem?

Thanks so much,

JS

 

[JLGWhiz]

You could design your own userform using labels or multipage and have almost
limitless space for text.

 

[Peter T]

I have tried, unsuccessfully, to use a VBA textform (over my head I

guess).

Maybe the following will get you started

Add a userform, alt-i, u.
If the Toolbox doesn't appear look for the spanner and hammer icon
Add a Textbox named TextBox1 and two buttons named CommandButton1 & 2 (hover
over the toolbox)

double click the form or press F7 to access the userform's code module

Private Sub CommandButton1_Click()
' this is the Cancel button
' in properties set Cancel=True
Me.TextBox1.Text = ""
Me.Hide
End Sub

Private Sub CommandButton2_Click()
' This is the OK button
Me.Hide
End Sub

Insert a normal module and paste the following

Sub EditText()
Dim sTextOrig As String, sTextNew As String
Dim frm As UserForm1

sText = "a whole bunch of text"

sText = sText & vbLf & _
"use Ctrl-Enter to force new line but ensure, "
sText = sText & "the Textbox's Multiline property is True"
Set frm = New UserForm1
frm.TextBox1.Text = sText

frm.Show vbModal
sTextNew = frm.TextBox1.Text

If Len(sTextNew) Then
MsgBox sTextNew
Else
MsgBox "user cancelled"
End If

End Sub


Go back to the form menu
Select CommandButton1
In properties set
Cancel = True
and change the caption to "Cancel"

Select CommandButton1
change the caption to "OK"

Select Textbox1
change Multiline = True and Wrap = True (if not already)


Switch back to the normal module, with the cursor in EditText() press F5 to
run


Regards,
Peter T

 

[Peter T]

I forgot a detail (closing with the little x), try this instead (on the form
a textbox and 2 commandbuttons with properties as described before)


'''''' userform code

Public sNewText As String

Private Sub CommandButton1_Click()
' this is the Cancel button
' in properties set Cancel=True
Me.TextBox1.Text = ""
Me.Hide
End Sub

Private Sub CommandButton2_Click()
' This is the OK button
sNewText = Me.TextBox1.Text
Me.Hide
End Sub


'''''' end userform code

' in a normal module

Sub EditText()
Dim sTextOrig As String, sTextNew As String
Dim frm As UserForm1

sText = "a whole bunch of text"

sText = sText & vbLf & _
"use Ctrl-Enter to force new line but ensure, "
sText = sText & "the Textbox's Multiline property is True"
Set frm = New UserForm1
frm.TextBox1.Text = sText

frm.Show vbModal
sTextNew = frm.sNewText 'extBox1.Text

If Len(sTextNew) Then
MsgBox sTextNew
Else
MsgBox "user cancelled"
End If

End Sub


Regards,
Peter T