vba 带选项的输入提示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17686656/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Input Prompt With Options
提问by VikkyB
Is there a way to produce input prompt having multiple options in the prompt itself. User can choose any one of the option provided. As of now I am doing it using 'Input string' and then setting the value of variable. Example: Which sheet to choose? Sheet1? Sheet2? Sheet3?
有没有办法在提示本身中生成具有多个选项的输入提示。用户可以选择提供的任一选项。截至目前，我正在使用“输入字符串”进行操作，然后设置变量的值。示例：选择哪张纸？表1？表2？表3？
回答by Vinnie
If you mean using the InputBox() function:
如果您的意思是使用 InputBox() 函数：
Answer: no you can't. InputBox is built into VBA and can't be modified.
回答：不，你不能。InputBox 内置于 VBA 中，无法修改。
However you CAN write your own InputBox. Do to this:
但是，您可以编写自己的 InputBox。这样做：
You must create a userform named formComboInput with a combo box, label, and button etc.
Create a public integer variable in the form's code called ReturnVal.
Assign the value of -1 to ReturnVal in the formComboInput.InialiseForm sub, and populate the combobox in that sub too.
In the userform button click code, assign value of formComboInput.comboInput.ListIndex to ReturnVal, and hide the form.
您必须创建一个名为 formComboInput 的用户表单，其中包含一个组合框、标签和按钮等。
在窗体的代码中创建一个名为 ReturnVal 的公共整数变量。
将值 -1 分配给 formComboInput.InialiseForm 子中的 ReturnVal，并填充该子中的组合框。
在userform按钮点击代码中，将formComboInput.comboInput.ListIndex的值赋值给ReturnVal，隐藏表单。
When the form loads, populate the combobox with a subroutine such as InitialiseForm(). You can store combo box ranges in a separate sheet or in static arrays.
当表单加载时，使用诸如 InitialiseForm() 之类的子例程填充组合框。您可以将组合框范围存储在单独的工作表或静态数组中。
Then insert code similar to below (untested sorry):
然后插入类似于下面的代码（未经测试抱歉）：
' User form code:
Option Explicit
public ReturnVal as integer
sub InitialiseForm()
dim i as integer
ReturnVal = -1
comboInput.Clear
for i = 1 to ThisWorkbook.Worksheets.Count ' Populates combobox
comboInput.AddItem ThisWorkbook.Worksheets(i).Name
next
end sub
btnOK_Click()
ReturnVal = comboInput.ListIndex ' Change ReturnVal from -1 to the listbox index
Me.Hide
End Sub
' Module/sheet code:
Option Explicit
function ShowComboInput(InputBoxCaption as String, DefaultResult as String) as String
with formComboInput
.InitialiseForm() ' Make the combo box populate etc
.labelCaption = InputBoxCaption
.Show vbModal ' CODE EXECUTION PAUSES HERE UNTIL FORM IS CLOSED
if .ReturnVal > -1 then
ShowComboInput = .comboInput.Value ' Returned if user clicks OK button
else
ShowComboInput = DefaultResult ' Returned if user closes form
end if
end with
end function
sub InputBoxExample() ' Call this sub to test the above code
MsgBox ShowComboInput("Testing", "User didn't click OK button!")
end sub
This code is untested so may need some tweaking but in general this is how I would implement a custom input box.
此代码未经测试，因此可能需要进行一些调整，但总的来说，这就是我实现自定义输入框的方式。
回答by szeta
you can create a list with values (simply some cells in another sheet), mark the list and give the selection a name (usually the field left of the formula field), e.g. "myList" and then instead of input string, you select type "list" and give the parameter =myList.
您可以创建一个包含值的列表（只是另一个工作表中的一些单元格），标记列表并为选择命名（通常是公式字段左侧的字段），例如“myList”，然后选择类型而不是输入字符串“list”并给出参数=myList。
