有受保护的工作表时如何保持宏运行? [英] How to keep macro running when there are protected sheets?

问题描述

我用密码保护了工作表4，因为在工作表4的那些单元格中不允许用户输入某些单元格.密码是1234.

I have protected sheets 4 with a password because there are some cells that users aren't allowed to input in those cells in sheet 4. The password is 1234.

但是，我想运行我的宏，如果有错误，该单元格将自动突出显示.

But, I want to run my macro, and if there is an error, the cell will be highlight automatically.

我的宏无法运行并且出错，因为我要突出显示的单元格位于受保护的工作表中.

My macro doesn't run and error, because the cell that I want to highlight is in protected sheet.

当单击验证按钮时，如何使工作表4保持受保护状态并使宏继续运行?

How to make the sheet 4 stay protected and make my macro keep running when I click the validation button?

Private Sub commandbutton1_click()

FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:=1234, WriteResPassword:=1234, _
    ReadOnlyRecommended:=False, CreateBackup:=False

vehicle = Sheets("4").Range("K22")

expenditure_gasoline = Sheets("4").Range("M22")


If vehicle = true and expenditure_gasoline = 0 Then
        MsgBox "it should not be empty", vbcritical

End If

If vehicle = true and expenditure_gasoline = 0 Then Sheets("4").Range("M22").Interior.ColorIndex = 3


End sub

推荐答案

尝试以下更改(未经测试)

Try the changes bellow (untested)

V1 -保护工作表不受用户更改，但不受VBA更改的影响 UserInterfaceOnly:= True

V1 - Protect the sheet from user changes, but not VBA changes UserInterfaceOnly:=True

Option Explicit

Private Sub commandbutton1_click()

    Const PATH_AND_FILENAME = "C:\YourTestFile.xlsx" '<------ Update this path & file name

    Dim wb As Workbook, ws As Worksheet, vehicle As Variant, expenditureGasoline As Variant

    Set wb = Workbooks.Open(Filename:=PATH_AND_FILENAME, WriteResPassword:="1234", _
                            Password:="1234", Format:=xlOpenXMLWorkbookMacroEnabled)
    Set ws = wb.Sheets("4")

    ws.Protect Password:="1234", UserInterfaceOnly:=True '<--- Protect changes from UI only

    Set vehicle = ws.Range("K22")
    Set expenditureGasoline = ws.Range("M22")

    If Not IsError(vehicle) And Not IsError(expenditureGasoline) Then
        If vehicle = True And expenditureGasoline = 0 Then
            ws.Range("M22").Interior.ColorIndex = 3
            MsgBox "Cell M22 should not be empty", vbExclamation
        End If
    End If
End Sub

---

V2 -更改前取消保护，更改后重新保护

---

V2 - Unprotect before the change, and Protect back after the change

Private Sub commandbutton1_click()

    Const PATH_AND_FILENAME = "C:\YourTestFile.xlsx" '<------ Update this path & file name

    Dim wb As Workbook, ws As Worksheet, vehicle As Variant, expenditureGasoline As Variant

    Set wb = Workbooks.Open(Filename:=PATH_AND_FILENAME, WriteResPassword:="1234", _
                            Password:="1234", Format:=xlOpenXMLWorkbookMacroEnabled)
    Set ws = wb.Sheets("4")
    Set vehicle = ws.Range("K22")
    Set expenditureGasoline = ws.Range("M22")

    If Not IsError(vehicle) And Not IsError(expenditureGasoline) Then
        If vehicle = True And expenditureGasoline = 0 Then

            ws.Unprotect "1234"                     '<--- Unprotect it before the change
            ws.Range("M22").Interior.ColorIndex = 3
            ws.Protect "1234"                       '<--- Protect it back, after the change

            MsgBox "Cell M22 should not be empty", vbExclamation
        End If
    End If
End Sub

这篇关于有受保护的工作表时如何保持宏运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！