Looping Macro

[crazyeyeschase]

I have a macro that changes a few things when the version is changed. The version is set in another workbook that is why i am using "Worksheet_Calculate". I have found a few issues with this because the entire WS is protected as you can see below so i need to remove this all together when i'm doing a lot of work.

(SIDE NOTE)If you know of a better way to run this when a single cell that contains the version is changed please let me know.

The issue right now aside from that is that when i start adding more and more cell changes i start creating a loop. The areas in bold and red are the areas i know are causing the loop. I have removed the red areas adn tested and it works fine. I have aso reinserted each red line on its own and i get the loop.

What can i do differantly to prevent the loop?

Private Sub Worksheet_Calculate()
Dim psw As String
Workbooks("All Items.xlsm").Activate

MsgBox "woot"

Pwd = "*****"
With ActiveSheet
    If Range("A8").Value = "1.0" Then
        ActiveSheet.Unprotect Password:=Pwd

        Range("F456").Style = "data"
        [COLOR=#ff0000][B]Range("F456").Formula = "=SUM(F659*4)"[/B][/COLOR]
        [COLOR=#ff0000][B]Range("D456").Value = "415*4"[/B][/COLOR]

        Range("F659").Style = "insert"
        [B][COLOR=#ff0000]Range("E659").Value = "N/A"[/COLOR][/B]
        ActiveSheet.Protect Password:=Pwd
        Else
        If Range("A8").Value = "2.0" Then
            ActiveSheet.Unprotect Password:=Pwd

            Range("F456").Style = "insert"
           [COLOR=#ff0000] [B]Range("E456").Value = "N/A"[/B]
            [B]Range("D456").Value = "N/A"[/B][/COLOR]

            Range("F659").Style = "1.8"
            ActiveSheet.Protect Password:=Pwd
        End If
    End If
End With
End Sub

 

[StephenCrump]

I assume it's the user, and not a formula, that will change the value of cell A8? If so, you can do two things.

1. Run the code only if the user changes the cells you're interested in.

2. Use Application.EnableEvents to stop the code triggering itself (or other events)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A8")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    'Make changes here
    
    Application.EnableEvents = True
        
End Sub

 

[StephenCrump]

Sorry, I just noticed you're using a second workbook:

There's a difference between:

With ActiveSheet
        Range("F456").Formula = "=SUM(F659*4)"

and

With ActiveSheet
        .Range("F456").Formula = "=SUM(F659*4)"

The former will change F456 in the sheet that has the code, not the ActiveSheet in the different workbook.