Multi-line input box

The standard VBA.InputBox (and the Application.InputBox if working in Excel) only allow you to enter a single line of text. What to do if you want to allow a user to enter multiple lines of text?

A couple of fairly hopeless options are:

• Show an InputBox multiple times (once for each line), or

• Show an InputBox just once and inform the user of some special character (eg "\") that can be entered into the text of the InputBox and will be used to create a new line (in code, you would use such as Replace(sReplyFromInputBox, "\", vbNewLine) to convert the InputBox reply into the multiple lines)

A better option is to use a UserForm with a multi-line TextBox that could look like this ...

In the following code (and ... more usefully ... in the downloadable UserForm at the end of this post), the title, prompt, default text, text of the buttons and the size of the TextBox can all be customised. You can omit the prompt entirely if you want it and you don't have to provide any default text. There is also a MsgBox shown (that you can customise the text of) if the user clicks 'Cancel' or if the user clicks 'OK' when no text is entered.

This code is an example of showing the UserForm and getting the text that the user enters (if OK was clicked) ...

The following is the full code of the UserForm. However, of course, the code is only half the story with a UserForm, so this code might be useful to review and understand how it all works, but to actually use the UserForm in your VBA Project, I strongly suggest you download the UserForm files and install them into your Project.

Note that there are 2 TODOs in the code (towards the end of the Display method) that you should review, especially if using the UserForm in a host application other than Excel. To find the TODOs, use the VBE's Find dialog and find the text "TODO". Alternatively, use VBE_Extras ... see my Sometimes it's just the little things - #4: Tasks (aka TODOs) post.

If you want to read more about the 'button hover procedures' see post Changing UserForm Button colour on mouse hover.

Here's the entire UserForm ...

UserFormInputText.zip

Download ZIP • 4KB

Once downloaded, unzip it and then import it directly into your VBA Project ... in the Project window, right click on the Project name or on any Module name then select "Import File" from the context menu, locate the unzipped UserFormInputText.frm file then click "Open". Note that the UserFormInputText.frx file must be in the same folder when importing the .frm file ... the former is the UserForm code and the latter is the UserForm layout.

A final tip ... when working with UserForms, I find it really frustrating that the VBE moves the cursor location to the UserForm designer after each time code runs that shows a UserForm (to see what I mean, add UserFormInputText and add the TestUserFormInputText procedure then put your cursor in the TestUserFormInputText procedure and press F5 or run code via the main menu: Run > Run Sub/UserForm). To get the cursor back to where it was before the code was run, you have to manually navigate back to the appropriate Module and then, within that, locate the appropriate procedure. However, if you use VBE_Extras then you can get the cursor back with a single press of Ctrl + - (that is, the Ctrl key and the "minus" key) if using the default keyboard shortcuts (if not, in the menu: Extras > Goto > History back, or use the button with the same caption in the VBE_Extras toolbar). To read more on using the 'cursor history' feature of VBE_Extras, see the Tracking your cursor location history in the VBE post.