Looping Macro

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
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?

Code:
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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)

Code:
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Sorry, I just noticed you're using a second workbook:

There's a difference between:

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

and

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top