CODEWITHSUNDEEP
vbaexcel
napi15
napi15

Reputation: 2402

What I'm doing wrong in reset Cell value excel

As I'm, deeply new to excel programming I figure out a macro that help me validate cells and set cells to false if the value in another cell do not allow it 

   Private Sub Worksheet_Change(ByVal Target As Range)
 ActiveSheet.Unprotect
Range("W" & (ActiveCell.Row)).Locked = False
Range("Y" & (ActiveCell.Row)).Locked = False
Range("Z" & (ActiveCell.Row)).Locked = False
Range("C" & (ActiveCell.Row)).Locked = False
If Range("U" & (ActiveCell.Row)).Value = "Controlled Good" Then
Range("W" & (ActiveCell.Row)).Locked = True
End If

But I want to reset the value of the none wanted cell back to empty , so I tried 

 If Range("U" & (ActiveCell.Row)).Value = "Controlled Good" Then
Range("W" & (ActiveCell.Row)).value=""
Range("W" & (ActiveCell.Row)).Locked = True

But I'm receiving an error , what I'm doing wrong?

thanks

Upvotes: 2

Views: 144

Answers (2)

napi15
napi15

Reputation: 2402

Here we go :) this worked too

    If Range("U" & (Target.Row)).Value = "Controlled Good" Then
Range("W" & (Target.Row)).Locked = True
 Application.EnableEvents = False
Range("W" & (Target.Row)).Value = ""
Application.EnableEvents = True
End If

thanks to MS Excel crashes when vba code runs

Upvotes: 1

Chicago Excel User
Chicago Excel User

Reputation: 263

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
 With ActiveSheet
        .Unprotect

.Range("W" & (ActiveCell.Row)).Locked = False
.Range("Y" & (ActiveCell.Row)).Locked = False
.Range("Z" & (ActiveCell.Row)).Locked = False
.Range("C" & (ActiveCell.Row)).Locked = False
If .Range("U" & (ActiveCell.Row)).Value = "Controlled Good" Then
.Range("W" & (ActiveCell.Row)).Value = ""
.Range("W" & (ActiveCell.Row)).Locked = True
End IF
        .Protect

    End With


End If

Upvotes: 2

Related Questions