Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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
excel
MS Office Version: Excel 2007
posted by: MayDay1988 (rep: 4) on Sun Mar 26, 2017 at 9:55 pm
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))
posted by: cappymer1 (rep: 120) on Mon Mar 27, 2017 at 5:28 am
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