What I'm doing wrong in reset Cell value excel

Question

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

what happens when you change all ActiveCell.Row to Target.Row ? — Chicago Excel User, Aug 04 '15 at 21:31
[This](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) will get you started... — Siddharth Rout, Aug 04 '15 at 21:35
@ChicagoExcelUser Hi, same error witch is : Method 'Locked ' of object 'Range' failed — napi15, Aug 04 '15 at 21:39
@SiddharthRout this seens to work ! I will verify it and post it as an resolved once Im 100% sure it's working Application.EnableEvents = False Range("W" & (Target.Row)).Value = "" Application.EnableEvents = True — napi15, Aug 04 '15 at 21:46

score 2 · Accepted Answer · answered Aug 04 '15 at 21:52

2

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

answered Aug 04 '15 at 21:52

Chicago Excel User

263
1
9

Yup ! I discovered that in fact I must shutdown the application event like this Application.EnableEvents = False Range("W" & (Target.Row)).Value = "" Application.EnableEvents = True – napi15 Aug 04 '15 at 21:55
That makes sense. It will keep looping otherwise. – Chicago Excel User Aug 04 '15 at 21:59

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

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

edited May 23 '17 at 12:06

Community

1
1

answered Aug 04 '15 at 21:53

napi15

2,354
2
31
55

What I'm doing wrong in reset Cell value excel

2 Answers2