vba - 编译错误: Expected End Sub

Question

我有一个分配给某个形状的宏，并且想要用密码保护该宏，以便在单击该形状时会出现一个弹出框，要求输入密码，理想情况下，我想通过用户表单来执行此操作。

我看过这个问题:How do you password protect excel VBA macro from running并做了我相信回答者所说的事情，所以我的代码如下:

Sub EmailExtract()

UserForm1.Show

***Code for the macro then follows***

End Sub

然后是用于单击按钮的用户表单:

Private Sub CommandButton1_Click()
If TextBox1.Value = "Password" Then 'Replace Password by your custom password
    Sub EmailExtract() 'This is the sub that was being called by your button.
Else
    MsgBox "You are not allowed to launch the macro"
End If
Exit Sub
End Sub

但是当我尝试运行此程序时，我收到错误Compile error: Expected End Sub 行:If TextBox1.Value = "Password"then

有人可以告诉我我做错了什么吗？

Answer 1

• 您必须在 Exit Sub 之后使用 End If 关闭 If 语句
• 并且您正在 Sub 中调用 Sub，这会引发 End Sub 错误，您只需放入 EmailExtract

参见下面的代码:

Private Sub CommandButton1_Click()
If TextBox1.Value = "Password" Then 'Replace Password by your custom password
    EmailExtract 'This is the sub that was being called by your button.
Else
    MsgBox "You are not allowed to launch the macro"
Exit Sub
End If
End Sub

更新:

这是一种不同的密码保护方法，只是使用 InputBox 而不是 UserForm 来收集和检查密码值。

确保您使用密码保护您的 VBA 代码，否则任何知道如何检查代码并从代码中获取密码的人都可以。

Sub EmailExtract()
Dim Message As String, Title As String, Password As String
Message = "Enter the macro password"    ' Set prompt.
Title = "Macro Password"    ' Set title.
Password = InputBox(Message, Title)
If Password = "Password Here" Then

    ''***Code for the macro then follows***

Else
    MsgBox "You are not allowed to launch the macro"
    Exit Sub
End If
End Sub

第二次更新:

通过这种方式，您可以创建一个 Sub 来调用 UserForm，然后验证密码输入，之后调用 sub EmailExtract() 并运行所需的代码。

通过UserForm使用密码保护的方法如下:

显示UserForm(由您的形状调用):

Sub UserFormShow()

UserForm1.Show

End Sub

进行密码验证:

Private Sub CommandButton1_Click()
If TextBox1.Value = "Password" Then 'Replace Password by your custom password
    EmailExtract 'The new sub your going to call
Else
    MsgBox "You are not allowed to launch the macro"
Exit Sub
End If

End Sub

运行您的代码(新子):

Sub EmailExtract()

***Code for the macro then follows***

end sub