Testing single code, work fine. Put multiple codes in one sheet, one code doesn't work.

0

I tested each code separately and they are working fine. When I put them together in one sheet the third one where I clear COL K if COL J not equal 100% doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Locked Cell after verified
    If Not Intersect(Target, Columns(11)) Is Nothing Then
        If Target.Value = "Completed" Then
            ActiveSheet.Unprotect ("PASSWORD")
            Range("U" & Target.Row & ":AP" & Target.Row).Locked = True
            ActiveSheet.Protect ("PASSWORD")
        Else
            ActiveSheet.Unprotect ("PASSWORD")
        Range("U" & Target.Row & ":AP" & Target.Row).Locked = False
         'Optional, reprotect sheet
        ActiveSheet.Protect ("PASSWORD")
        End If
    End If
    'Prevent Duplicate Name
    If Target.Cells.Count > 1 Or IsEmpty(Target(1, 1)) Then Exit Sub
    If Not Intersect(Target, Range("W:AP")) Is Nothing Then
        If WorksheetFunction.CountIf(Range("W" & Target.Row & ":AP" & Target.Row), Target) > 1 Then
            MsgBox Target & " already exists. Please select new name."
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If
    'Clear verification if record not equal 100%
    Dim rLook As Range, r As Range, Intr As Range
    Set rLook = Range("J:J")
    Set Intr = Intersect(rLook, Target)
    If Intr Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each r In Intr
            If r.Value <> "1" Then
               rw = r.Row
                Range("K" & rw).ClearContents
            End If
        Next r
    Application.EnableEvents = True
End Sub
Answer
Discuss

Discussion

Please put CODE tags around your code. To do that, edit your post, select your code and click the CODE button.
cappymer1 (rep: 120) Mar 27, '17 at 5:16 am
Add to Discussion

Answers

0

In which way is it not working? It works for me when I go to edit a cell in column J. Might it have something to do with the first part re-protecting the worksheet and then the macro can't edit the sheet as you want? (I had to manually unprotect the sheet to test the column J functionality.)

Also, it only works if you edit a cell in column J.

It could also have to do with the way that you are testing the spreadsheet, as in, if you filled-in the required data fields or not etc. and this could be a part of that problem issue:

If Target.Cells.Count > 1 Or IsEmpty(Target(1, 1)) Then Exit Sub

Update

Based on your comment, the reason it isn't working is because you are checking for column J to change instead of checking for the cells that you will actually change, to which column J is linked with a formula.

You can update this line of code to check for the cells that will actually change:

Set rLook = Range("J:J")

Or, add another macro to the Worksheet like this:

Private Sub Worksheet_Calculate()

If Range("J1").Value <> 1 Then
    'Code to clear the cell
End If

End Sub

This example is hard-coded to work only for cell J1, but you can make it dynamic as you need.

The real difference is that this will run each time a formula is calculated within the worksheet, which is what is happening in column J in your worksheet.

Update 2

This line of code is preventing a delete from happening:

If Target.Cells.Count > 1 Or IsEmpty(Target(1, 1)) Then Exit Sub

You can remove this part if you want it to work as described in the comments:

Or IsEmpty(Target(1, 1))
Discuss

Discussion

Ah! To the point, I think it has to do with the lock cell. Becsides the code, I locked COL K for editing may be that's why it won't clear COL K when I tested. Is there a way around this? To add a code that temporary unlock K and clear content then relock again?

Thank you cappymer1!
MayDay1988 (rep: 4) Mar 27, '17 at 5:49 am
You can use the code from higher in the macro that unprotects the worksheet and then re-protects it.
ActiveSheet.Unprotect ("PASSWORD")
'Your code
ActiveSheet.Protect ("PASSWORD")
cappymer1 (rep: 120) Mar 27, '17 at 5:51 am
I tried this morning, it wasn't about the lock permission.

However, found something, my COL J isn't a direct input but a formular. When I change COL J directly and the code worked fine. But with the current formular it doesn't work (COL J =(H2/G2) - H and G are also not exact value they are referencing from other cells)

Is there a way to fix this?

Thanks!
MayDay1988 (rep: 4) Mar 27, '17 at 6:30 pm
Updated the answer and it should give you some options.
cappymer1 (rep: 120) Mar 28, '17 at 5:56 am
I'm almost there, everything seem to work fine when I change J:J to my target range W:AP. However, I want K to clear everytime there is an update whether it is changing value or delete value (blank). I tested single code in separate sheet and it work fine, but when I put all my codes together. COL K only clear when I make change to W:AP but when I delete value from W:AP, COL K doesn't clear.

Thank you!

It said my too long I can't put code in this comment please see below.
MayDay1988 (rep: 4) Mar 29, '17 at 2:59 am
Private Sub Worksheet_Change(ByVal Target As Range)
  'Locked sheet
  ActiveSheet.Protect ("PASSWORD")
 
  'Locked Cell after verified
  If Not Intersect(Target, Columns(11)) Is Nothing Then
    If Target.Value = "Completed" Then
      ActiveSheet.Unprotect ("PASSWORD")
      Range("U" & Target.Row & ":AP" & Target.Row).Locked = True
      ActiveSheet.Protect ("PASSWORD")
    Else
      ActiveSheet.Unprotect ("PASSWORD")
    Range("U" & Target.Row & ":AP" & Target.Row).Locked = False
    ActiveSheet.Protect ("PASSWORD")
    End If
  End If
 
  'Prevent Duplicate Name
  If Target.Cells.Count > 1 Or IsEmpty(Target(1, 1)) Then Exit Sub
  
  If Not Intersect(Target, Range("W:AP")) Is Nothing Then
    If WorksheetFunction.CountIf(Range("W" & Target.Row & ":AP" & Target.Row), Target) > 1 Then
      MsgBox Target & " already exists. Please select new name."
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
    End If
  End If
   
MayDay1988 (rep: 4) Mar 29, '17 at 3:00 am
Code continue

  'Clear verification if record changes
  Dim rLook As Range, r As Range, Intr As Range
  Set rLook = Range("W:AP")
  Set Intr = Intersect(rLook, Target)
  If Intr Is Nothing Then Exit Sub
 
  Application.EnableEvents = False
    For Each r In Intr
      If r.Value <> "" Then
       rw = r.Row
        ActiveSheet.Unprotect ("PASSWORD")
        Range("K" & rw).ClearContents
      End If
    Next r
    Application.EnableEvents = True

    For Each r In Intr
      If r.Value = "" Then
       rw = r.Row
        ActiveSheet.Unprotect ("PASSWORD")
        Range("K" & rw).ClearContents
      End If
    Next r
  Application.EnableEvents = True
End Sub
MayDay1988 (rep: 4) Mar 29, '17 at 3:00 am
I will take a look at it later, but next time you need to post a lot of code, just update your question the same way I Updated my answer with an "Update" heading and you can post everything there.
cappymer1 (rep: 120) Mar 30, '17 at 10:17 pm
Update in answer
cappymer1 (rep: 120) Mar 31, '17 at 1:05 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login