VBA code not working
On May 21,2022 by Tom Routley
I have the following VBA code :
Issue
Private Sub Worksheet_Change(ByVal Target As Range) Dim cRow As Integer If Intersect(Target, Range("R11:R20")) Is Nothing Then Exit Sub If Target.Value = "F" Then ActiveSheet.Unprotect cRow = Target.Row Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = True ActiveSheet.Protect End If If Target.Value = "T" Or Target.Value = "" Then ActiveSheet.Unprotect cRow = Target.Row Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = False ActiveSheet.Protect End If End Sub
What I need is to ClearContents for (cRow target.Row) in the upper case first before it is locked.3
I have Excel sheet to calculate overtime, includes several rows for each month, and the sheet is protected but allowing the user to enter data into some specified range (P11:P20,R11:X20) (Allow user to edit range).
Also, the Range (R11:R20) are rows with drop list menu of 4 values (1,2,3,4).
My case: For example, if the user selected either value 3 or 4 ONLY in cell (R15) I need to block or protect the range (T15:U15), and if then with the same sheet selected 3 or 4 value in cell R19 to protect the range (T19:U19) and so on.
Therefore I need if some value selected in specific cell to protect some ranges within the same row. Please note that the user may select one selection or multi selection in the same sheet.
Solution
For your current query I assumed you want to clear the contents of column T and U of the target row.
Private Sub Worksheet_Change(ByVal Target As Range) Dim cRow As Integer If Intersect(Target, Range("R11:R20")) Is Nothing Then Exit Sub If Target.Value = "F" Then ActiveSheet.Unprotect cRow = Target.Row With Range(Cells(cRow, "T"), Cells(cRow, "U")) .ClearContents .Locked = True End With ActiveSheet.Protect End If If Target.Value = "T" Or Target.Value = "" Then ActiveSheet.Unprotect cRow = Target.Row Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = False ActiveSheet.Protect End If End Sub
Thanks to TrowaD for this tip.
Leave a Reply