CODEWITHSUNDEEP
excelvba
Wtr
Wtr

Reputation: 39

limit a excel vba macro to one file

I'm running a macro that automaticaly protects a worksheet when a cell value changes (with password). This macro runs all the time in all open sheets. This is supposed to be so. But when I have another excel file open, it also protects that sheet with the password. How can I limit my macro to only the file it is in?

Thanks!

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

Upvotes: 0

Views: 3129

Answers (1)

Trace
Trace

Reputation: 18859

This is a perfect example on how ActiveSheet opens up possible bugs and should be avoided as much as possible.

The first thing you need to do is always respect the following object hierarchy: 

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

Translated into VBA this means that it is good practice to ALWAYS write down the entire hierarchy: 

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

If you use multiple application objects or, more often workbooks, you have control on the objects that you want to manipulate.

Now instead of using: 

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

You would have: 

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

Now in case you need to loop through multiple sheets, you can always use the sheet index instead of sheet name. For example: 

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

Upvotes: 1

Related Questions