vba 调用用户表单并返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18966137/
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
Calling a userform and returning a value
提问by user2385809
I have a vba code thats Auto_Open. It does some checks then prompts a userform that asks for username and password. I called this userform with userform_name.show.
我有一个 Auto_Open 的 vba 代码。它会做一些检查，然后提示一个用户表单，要求输入用户名和密码。我用userform_name.show.
My issue is how can I return a Booleanto my Auto_Opensub from the userform code.
我的问题是如何从用户表单代码中将a 返回Boolean到我的Auto_Open子程序。
I linked the code that verifies if the credentials are correct to the "Login" button on the form. this is the code that produces the Boolean. I need to return it to the Auto_Open.
我将验证凭据是否正确的代码链接到表单上的“登录”按钮。这是产生布尔值的代码。我需要将它返回到 Auto_Open。
Private Sub loginbutton()
Dim bool As Boolean
Dim lrup
Dim r As Long
Dim pass As String
loginbox.Hide
'are fields empty
Do While True
If unBox.Text = "" Or pwBox.Text = "" Then
MsgBox ("You must enter a Username and Password")
Else
Exit Do
End If
loginbox.Show
Exit Sub
Loop
'find pw reated to username (if existant)
lrup = UserPass.Range("A1").Offset(UserPass.Rows.Count - 1, 0).End(xlUp).Row
If unBox = "b0541476" And pwBox = "theone" Then
bool = True
Else
MsgBox ("Invalid username or password. Please try again.")
loginbox.Show
Exit Sub
End If
For r = 2 To lrup
If unBox = Cells(r, 1) Then
pass = Cells(r, 2).Value
Exit For
End If
Next
If pass = "" Then
MsgBox ("Invalid username or password. Please try again.")
loginbox.Show
Exit Sub
Else
bool = True
End If
End Sub
采纳答案by Siddharth Rout
Remove Dim bool As Booleanfrom the userform code area and declare it in the module as shown below
Dim bool As Boolean从userform代码区移除，在模块中声明如下
This is how your Code in the module would look like
这就是您在模块中的代码的样子
Public bool As Boolean
Sub Auto_Open()
'
'~~> Rest of the code
'
UserForm1.Show
If bool = True Then
'~~> Do Something
Else
'~~> Do Something
End If
'
'~~> Rest of the code
'
End Sub
回答by Portland Runner
How about using a function instead of a sub?
如何使用函数而不是子函数？
Function loginbutton()
' your code
loginbutton = bool
End Function
Now in your calling code you can test for true/false
现在在您的调用代码中，您可以测试真/假
if loginbutton() then
'true responce
else
'false responce
end if
回答by Noodle_Soup
You can manage to do this without the use of public variables.
您可以在不使用公共变量的情况下设法做到这一点。
There appears to be a difference between show/hide and load/unload.
显示/隐藏和加载/卸载之间似乎存在差异。
If you hide a form while it's still loaded it won't be cleared out, so you can reference the state of the controls on the form.
如果在加载时隐藏表单，它不会被清除，因此您可以引用表单上控件的状态。
For example I was using a date picker (called DTPicker1) on a form, my code in the module looks something like this:
例如DTPicker1，我在表单上使用日期选择器（称为），模块中的代码如下所示：
Dim NewDay As Date
Load FrmDayPicker
FrmDayPicker.Show
NewDay = FrmDayPicker.DTPicker1.Value
Unload FrmDayPicker
Debug.Print NewDay
On your form you can just use Me.Hideinsteaded of Unload Meand this should work
在您的表单上，您可以使用Me.Hide代替，Unload Me这应该可以工作
