crazyeyeschase
Board Regular
- Joined
- May 6, 2014
- Messages
- 104
- Office Version
- 365
- Platform
- Windows
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?
(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