How to get this VBA code to run on all rows

libcat

New Member
Joined
Nov 30, 2021
Messages
10
Platform
  1. Windows
Hi everyone,

I have a code running that locks and unlocks cells in a row based on the values in Column A and B in that row.
So for example when you fill in a value in Row 3 column A or B, checks if the values meet the criteria:
Row 3 Column A is "In Process" and Row 3 Column B is not empty
If the criteria is met, Row 3 Columns E and onwards become unlocked.
If the criteria is not met, Row 3 Columns E and onwards become locked.

This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim rng As Range
If Not Intersect(Target, Range("a:a")) Is Nothing Or Not Intersect(Target, Range("b:b")) Is Nothing Then
ActiveSheet.Unprotect ""
i = Target.Row
Set rng = Range(Cells(i, 5), Cells(i, 130))
If Cells(i, 1) = "In Process" And Not IsEmpty(Cells(i, 2)) Then
rng.Locked = False
Else
rng.Locked = True
End If
ActiveSheet.Protect "", _
AllowFiltering:=True
End If
End Sub


It works when you edit one cell at a time, but if you use a copy-down to fill in cells A or B on multiple rows in bulk, it only performs the code on the first edited row.
I am looking to either:
a) rewrite the code so it is capable of checking more than 1 row at once
OR
b) make a "recalculate lock/unlock on all rows" button with a macro that goes through each row, edits a value in column A or B in each row, thus triggering the original code to run


Any ideas, geniuses?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

gordsky

Well-known Member
Joined
Jun 2, 2016
Messages
530
Office Version
  1. 2016
Platform
  1. Windows
You could wrap your existing code in a loop based on the number of cells in the new target. Should work for copy/paste and also filldown.
Maybe something like

VBA Code:
Private Sub Worksheet_Change(ByVal Target As range)

Dim i As Long
Dim rng As range

If Not Intersect(Target, range("a:a")) Is Nothing Or Not Intersect(Target, range("b:b")) Is Nothing Then
    ActiveSheet.Unprotect ""

    For i = Target.Row To Target.count
        Set rng = range(Cells(i, 5), Cells(i, 130))
        
        If Cells(i, 1) = "In Process" And Not IsEmpty(Cells(i, 2)) Then
            rng.Locked = False
        Else
            rng.Locked = True
        End If


    Next i

End If

ActiveSheet.Protect "", _
AllowFiltering:=True

End Sub
 

libcat

New Member
Joined
Nov 30, 2021
Messages
10
Platform
  1. Windows
You could wrap your existing code in a loop based on the number of cells in the new target. Should work for copy/paste and also filldown.
Maybe something like

VBA Code:
Private Sub Worksheet_Change(ByVal Target As range)

Dim i As Long
Dim rng As range

If Not Intersect(Target, range("a:a")) Is Nothing Or Not Intersect(Target, range("b:b")) Is Nothing Then
    ActiveSheet.Unprotect ""

    For i = Target.Row To Target.count
        Set rng = range(Cells(i, 5), Cells(i, 130))
       
        If Cells(i, 1) = "In Process" And Not IsEmpty(Cells(i, 2)) Then
            rng.Locked = False
        Else
            rng.Locked = True
        End If


    Next i

End If

ActiveSheet.Protect "", _
AllowFiltering:=True

End Sub
Do you mean to replace my existing VBA code?
Or as a sort of extra macro to recalculate all the cells?

Sorry if it's a silly question... I'm quite new to VBA & macros
 

gordsky

Well-known Member
Joined
Jun 2, 2016
Messages
530
Office Version
  1. 2016
Platform
  1. Windows
Do you mean to replace my existing VBA code?
Or as a sort of extra macro to recalculate all the cells?

Sorry if it's a silly question... I'm quite new to VBA & macros
replace the portion of code you posted with my code
 

Forum statistics

Threads
1,176,267
Messages
5,902,245
Members
434,954
Latest member
kchallr00

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