设置密码以通过VBA代替内置功能打开 [英] Set password for open by VBA instead of built-in feature

问题描述

我以前有一个有关为另一个工作簿设置密码的主题.另一个工作簿被命名为"Sample.xlsm"使用密码保护已关闭的工作簿

I have a previous topic about setting password for another workbook. The other workbook is named "Sample.xlsm" Protect closed workbook with password

现在，我需要让用户从Sample.xlsm本身输入密码，同时要防止用户更改密码

Now I need to make the user input the password from the Sample.xlsm itself and at the same time to prevent the user from changing the password

我用了这个

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.DisplayAlerts = False
        If SaveAsUI Then MsgBox "SaveAs Feature Disabled", vbExclamation: Cancel = True
    Application.DisplayAlerts = True
End Sub

但这似乎不足以阻止用户使用其他名称保存工作簿.

But this seems not enough from preventing the user to save the workbook with another name.

推荐答案

仅强制保存到特定位置:

To force only saving to a specific location:

这是应该停止大多数保存尝试的代码:

This is the code that should stop most attempts at saving:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True
    Call MySaveCode
BackToExcelSave:
    Application.EnableEvents = True
End Sub

然后，您必须添加代码进行保存，并避免触发excel的默认保存例程:

Then you have to add code for your save, and avoid triggering excels' default save routine:

Sub MySaveCode()

    On Error GoTo ReEnable ' Use On Error in case they cannot save to specified location/filename
    Application.EnableEvents = False ' turn off excel default action (Workbook_BeforeSave)
    Dim Path As String
    Dim FileName As String

    Path = "C:\Users\SeanC\Documents\Excel\"

    FileName = "MyFixedFilename.xlsm"

    Application.DisplayAlerts = False    'Optional. Suppresses default excel messages

    ThisWorkbook.SaveAs Filename:= _
            Path & FileName, _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
            CreateBackup:=False, _
            Password:="P@$$w0rd"

    MsgBox "Saved as: " & ThisWorkbook.FullName 'Also Optional

ReEnable:
     Application.DisplayAlerts = True    'Optional
     Application.EnableEvents = True
End Sub

这篇关于设置密码以通过VBA代替内置功能打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！