VBA code not working

February 2017




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


Published by aakai1056. Latest update on February 15, 2012 at 10:06 AM by aakai1056.
This document, titled "VBA code not working," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).