尝试以下更改(未经测试)
V1- 保护工作表免受用户更改,但不更改 VBAUserInterfaceOnly:=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- 更改前取消保护,更改后恢复保护
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