如何允许在VBA InputBox中输入多行输入? [英] How can I allow for the entering of multiple lines of input into a VBA InputBox?

问题描述

我正在处理一个宏，该宏将允许我将SQL查询键入(或更可能将其粘贴)到 InputBox 中，然后单击确定"，它会返回针对数据库查询.问题在于，默认情况下，VBA仅接受一行文本，并且SQL代码以更具结构性的多行格式编写，以提高可读性.当我尝试将SQL代码粘贴到 InputBox 时，除第一行文字外的所有文字都将被截断.我的下一个想法是在显示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!

推荐答案

我不认为您可以在InputBox中执行此操作.InputBoxes通常用于较小的输入，例如数字或单词.此外，在输入框内键入时按下 enter 会提交它，因此，如果您可以添加多行，将会造成混乱.

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.

您可以做的是创建一个新的UserForm，并添加一个TextBox，将 MultiLine 字段设置为 True .然后，只需添加一个确定"按钮即可关闭表单，然后从TextBox中读取宏.

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

它需要对 Microsoft Forms 2.0对象库

这篇关于如何允许在VBA InputBox中输入多行输入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！