Macro to request user type in information

June 4, 2009 at 01:44:21
Specs: Windows XP

I would like to write a macro that makes a small window pop-up (similar to an InputBox) that requests the user inputs a fair amount of text (a paragraph, or more) and allows multiple lines. This is then exported to a certain cell.

An InputBox only allows one single line of text. Is there some other type like this that would allow the user to type his text in? Similar actually to the text input box I'm using on this website. I can type lots of stuff, and start new lines if I like.

After that, once I have the information I'd like to be able to export this to a predefined cell. This is going to be for a report that users fill-in.

I can code the text exporting, I think, but could you please suggest how I'd make the text input stuff?

Thanks for any help!

See More: Macro to request user type in information

Report •

June 4, 2009 at 10:05:22
I don't really like it, but it works. If it does nothing more than give you some ideas, then I'm satisfied.


- The text box that accepts the user input is the only shape on your sheet.

- The users will actually follow the instructions. This is a very big assumption.

Sub CreateInputBox()
'Prompt user With Instructions
  MsgBox "Click OK to enter text in text box." _
         & vbCrLf & vbCrLf & _
         "Hit Esc Twice after entering text."
'Create User Input Box
  ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 240#, 209.25, 325.5, 219#).Select
'Set Esc key to export text
  Application.OnKey "{Esc}", "ExportText"
End Sub

Sub ExportText()
'Select Text Box
'Export text
  Range("A1") = Selection.Characters.Text
'Delete text box
'Reset Esc key
  Application.OnKey "{Esc}"
End Sub

Report •
Related Solutions

Ask Question