首页 文章

将 excel vba 宏限制为一个文件

提问于
浏览
0

我正在运行一个宏,该宏在单元格值更改(带有密码)时自动保护工作表。此宏始终在所有打开的工作表中运行。应该是这样。但是,当我打开另一个 excel 文件时,它也会用密码保护该工作表。如何将宏限制为仅包含它的文件?

谢谢!

Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim FormulaRange2 As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double

NotSentMsg = "Niet verzonden"
SentMsg = "Verzonden"

'Above the MyLimit value it will run the macro
MyLimit = 0

'Set the range with the Formula that you want to check
Set FormulaRange2 = ActiveSheet.Range("D22")

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange2.Cells
    With FormulaCell
        If IsNumeric(.Value) = False Then
            MyMsg = "Not numeric"
        Else
            If .Value > MyLimit Then

                MyMsg = SentMsg

                If .Offset(2, 10).Value = NotSentMsg Then
                    Call Mail_with_outlook2
                End If
            Else
                MyMsg = NotSentMsg
            End If
        End If
        Application.EnableEvents = False
ActiveSheet.Unprotect Password:="zou82pam"
        .Offset(2, 10).Value = MyMsg
ActiveSheet.Protect Password:="zou82pam", DrawingObjects:=True, Contents:=True,     
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
        Application.EnableEvents = True
    End With
Next FormulaCell

ExitMacro:
Exit Sub

EndMacro:
Application.EnableEvents = True

MsgBox "De onderstaande error is ontstaan. Neem contact op met Wouter van Leeuwen" _
     & vbLf & vbLf & Err.Number _
     & vbLf & Err.Description

End Sub

1 回答

  • 1

    这是有关ActiveSheet如何打开可能的错误的完美示例,应尽可能避免。

    您需要做的第一件事就是始终遵循以下对象层次结构:

    (application) -> workbook -> sheet -> range
    

    转换为 VBA,这意味着始终记下整个层次结构是一种好习惯:

    Thisworkbook.Sheets('sheetname').range("A1")
    

    如果使用多个应用程序对象或更经常使用的工作簿,则可以控制要操作的对象。

    现在而不是使用:

    ActiveSheet.Unprotect Password:="zou82pam"
            .Offset(2, 10).Value = MyMsg
    ActiveSheet.Protect Password:="zou82pam", DrawingObjects:=True, Contents:=True,     
    Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
            Application.EnableEvents = True
        End With
    

    你将会拥有:

    Thisworkbook.sheets("somesheet").Unprotect Password:="zou82pam"
            .Offset(2, 10).Value = MyMsg
    Thisworkbook.sheets("somesheet").Protect Password:="zou82pam", DrawingObjects:=True, Contents:=True,     
    Scenarios:=True
    Thisworkbook.sheets("somesheet").EnableSelection = xlUnlockedCells
            Application.EnableEvents = True
        End With
    

    现在,如果需要循环浏览多个工作表,则始终可以使用工作表索引而不是工作表名称。例如:

    Dim iCnt as integer
    
    for iCnt = 1 to 10
        Thisworkbook.sheets(iCnt).range("A1").value = "this is an example on how to use the sheet index"
    next iCnt
    

相关问题