Excel VBA - 将值从用户窗体组合框分配给全局变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14530146/
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
Excel VBA - Assign value from UserForm ComboBox to Global Variable
提问by P4U1
I have a userform with a basic combobox and command button. When the user hits the command button, I want the UserForm to close, and the value of the combobox to be saved in a variable that can be accessed by a subroutine contained within "ThisWorkbook".
我有一个带有基本组合框和命令按钮的用户表单。当用户点击命令按钮时，我希望用户窗体关闭，并将组合框的值保存在一个变量中，该变量可由“ThisWorkbook”中包含的子例程访问。
In the UserForm code:
在用户窗体代码中：
Public employee_position As String
Public Sub CommandButton1_Click()
employee_position = Me.ComboBox1.Value
Unload Me
End Sub
In the "ThisWorkbook" Code
在“本工作簿”代码中
Private Sub GetUserFormValue()
Call Userform_Initialize
EmployeePosition.Show
MsgBox employee_position
End Sub
When "GetUserFormValue()" runs, the UserForm comes up, you can select a value in the combobox and press the command button, but when the MsgBox comes up, it displays "" (Nothing)
当“GetUserFormValue()”运行时，用户窗体出现，你可以在组合框中选择一个值并按下命令按钮，但是当MsgBox出现时，它显示“”（无）
What am I doing wrong here?
我在这里做错了什么？
回答by Floris
When you Unload Me, I think you lose all information associated with the module (including the global variable). But if you use Me.Hide rather than Me.Unload, then you can access the value of the form after the routine returns. So try this:
当你Unload Me，我认为你失去了与模块相关的所有信息（包括全局变量）。但是如果您使用 Me.Hide 而不是 Me.Unload，那么您可以在例程返回后访问表单的值。所以试试这个：
-- userform code includes:
-- 用户表单代码包括：
Public Sub CommandButton1_Click()
Me.Hide
End Sub
-- main module includes:
-- 主要模块包括：
Private Sub GetUserFormValue()
Call Userform_Initialize
EmployeePosition.Show
MsgBox EmployeePosition.ComboBox1.Value
Unload EmployeePosition
End Sub
I think that should work.
我认为这应该有效。
回答by AndyUpNorth
I had the same problem, and this is how I resolved it:
我遇到了同样的问题，这就是我解决它的方法：
If the main code is in a worksheet, and the variable is declared as public in that worksheet (e.g. in Microsoft Excel Objects -> Sheet1 (Sheet1)), the result from "Unload Me" cannot be passed from a UserForm to the worksheet code.
如果主代码在工作表中，并且变量在该工作表中声明为公共（例如在 Microsoft Excel 对象 -> Sheet1 (Sheet1)），则“卸载我”的结果不能从用户窗体传递到工作表代码.
So to solve my problem, I inserted a new Module, and declared my public variable there. I didn't even have to move my code from the worksheet to the module... just the declaration of the public variable.
所以为了解决我的问题，我插入了一个新模块，并在那里声明了我的公共变量。我什至不必将我的代码从工作表移动到模块......只是公共变量的声明。
I hope this works for you too!
我希望这对你也有用！
Andrew
安德鲁
