Overcome VBA InputBox Character Limit

Question

Hi,

The current function I use to collect text InputBox can't accept more than 255 characters apparently, and I need to be able to collect more than that? Is there a parameter or different function I can use to increase this limit?

Answer 1

To be pedantic, the Inputbox will let you type up to 255 characters, but it will only return 254 characters.

Beyond that, yes, you'll need to create a simple form with a textbox. Then just make a little "helper function" something like:

Function getBigInput(prompt As String) As String
    frmBigInputBox.Caption = prompt
    frmBigInputBox.Show
    getBigInput = frmBigInputBox.txtStuff.Text
End Function

or something like that...

Answer 2

Thanks BradC for the info that. My final code was roughly as follows, I have a button that calls the form that I created and positions it a bit as I was having some issues with the form being in the wrong spot the everytime after the first time I used.

Sub InsertNotesAttempt()
    NoteEntryForm.Show
    With NoteEntryForm
        .Top = 125
        .Left = 125
    End With
End Sub

The userform was a TextBox and two CommandButtons(Cancel and Ok). The code for the buttons was as follows:

Private Sub CancelButton_Click()
    Unload NoteEntryForm
End Sub

Private Sub OkButton_Click()
    Dim UserNotes As String

    UserNotes = NotesInput.Text

    Application.ScreenUpdating = False
    If UserNotes = "" Then
        NoteEntryForm.Hide
        Exit Sub
    End If

    Worksheets("Notes").ListObjects("Notes").ListRows.Add (1)
    Worksheets("Notes").Range("Notes").Cells(1, 1) = Date
    Worksheets("Notes").Range("Notes").Cells(1, 2) = UserNotes
    Worksheets("Notes").Range("Notes").Cells(1, 2).WrapText = True
    ' Crap fix to get the wrap to work. I noticed that after I inserted another row the previous rows
    ' word wrap property would kick in. So I just add in and delete a row to force that behaviour.
    Worksheets("Notes").ListObjects("Notes").ListRows.Add (1)
    Worksheets("Notes").Range("Notes").Item(1).Delete
    NotesInput.Text = vbNullString
    NotesInput.SetFocus ' Retains focus on text entry box instead of command button.
    NoteEntryForm.Hide
    Application.ScreenUpdating = True
End Sub