CODEWITHSUNDEEP
excelvba
Cody Mayers
Cody Mayers

Reputation: 385

How can I allow for the entering of multiple lines of input into a VBA InputBox?

I'm working on a macro that would allow me to type (or more likely, to paste) a SQL query into an InputBox and upon clicking "OK", it returns the results of the query against the database. The problem is, by default VBA only accepts one line of text and SQL code is written in a more structured, multi-line format for readability. When I try to paste SQL code into the InputBox, all but the first line of text are truncated. My next idea is to read the contents of the clipboard and replace any newline characters with a space before displaying the InputBox and prompting me to enter a query, but obviously that wouldn't help with queries which I am typing in rather than pasting. Any help would be greatly appreciated!

Upvotes: 1

Views: 8087

Answers (1)

Josh Eller
Josh Eller

Reputation: 2065

I don't believe you can do this in an InputBox. InputBoxes are generally meant for small inputs, like a number or word. Additionally, hitting enter while typing into an InputBox submits it, so it would be confusing if you could add multiple lines.

What you can do instead is create a new UserForm, and add a TextBox with the MultiLine field set to True. Then, just add an 'OK' button that closes the form, and have your macro read from the TextBox.

As another alternative, here's a function I use to get whatever text is currently on the user's clipboard. So, you could just copy your query prior to running whatever macro you need to use it in.

Function copyFromClipboard() As String
    Dim clipboard As MSForms.DataObject
    Set clipboard = New MSForms.DataObject
    clipboard.GetFromClipboard
    copyFromClipboard = clipboard.getText
End Function

It requires a reference to Microsoft Forms 2.0 Object Library

Upvotes: 4

Related Questions