VBA code not working

December 2016




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.

Related :

This document entitled « VBA code not working » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.