I have a VBA macro that loops through cells whose values have been changed and amends the values of other cells in the same row.
Public Sub Worksheet_Change (ByVal Target As Range)
Dim r As Integer
Application.ScreenUpdating = false
' Unprotect the sheet
ActiveSheet.Unprotect("password")
Set newRange = Range("K:K")
If Not Application.Intersect(newRange, Range(Target.Address)) Is Nothing Then
For Each cell in Target.Cells
' Change the values of cells in the same row
r = cell.Row
Cells(r, 2).Value = "New Value"
Cells(r, 3).Value = "New Value" ' Debug highlights this line
Cells(r, 4).Value = "New Value"
Cells(r, 5).Value = "New Value"
Next
End If
' Reprotect the sheet
ActiveSheet.Protect Password:="password", AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Application.ScreenUpdating = True
End Sub
Without unprotecting/reprotecting the sheet the macro works fine, but when added it generates a Runtime Error Application-Defined or Object-Defined error, but not before changing the first cell value Cells(r, 2).Value = "New Value".
I can only assume that this is because the sheet is unprotected at the start and the first cell value change completes before the sheet is then locked (perhaps running in a separate thread to the For loop?). The macro then errors at the following line because it is trying to make a change to a protected sheet.
How can I fix this and prevent the sheet from locking too quickly?
You are changing cells with the event macro.
You must:
Application.EnableEvents = False
before the For loop and
Application.EnableEvents = True
after the For loop.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments