worksheet change event not working as planned

[Zabman]

Hi,

I created a worksheet change event with the idea that whenever a user enters a "yes" into column J, it would pop a user input. However, when I am deleting rows, it is erroring out. I have tried incorporating an 'on error resume next' but then it is popping up no matter what changes. It may be that I am putting this in the wrong place.

Can anyone help?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("$J:$J")) Is Nothing Then
        If UCase(Target) = "YES" Then
            Pub_DispFile = Range("Main_ShipRef").Offset(Target.Row - 2)
            Pub_CurrentRow = Target.Row - 2
            UFSplit.Show
        End If
    End If
End Sub

 

[Peter_SSs]

Your problem relates to this line

If UCase(Target) = "YES" Then

First, 'Target' is a range and you would be better to specify which property of that range you are referring to. Obviously the following since you are looking at the value, not the row, or the number of columns etc

If UCase(Target.Value) = "YES" Then

So when the code runs, it needs to evaluate the 'Value' of the 'Target'. However, when you are deleting a row the 'Target' consist of a range of hundreds or thousands (depends on Excel version) of cells. When you are dealing with multiple cells, what does 'Value' mean? They could all have different values.

You would have just the same problem with your code if you selected, say, J3:J4 and pressed the 'Delete' key (try it). Same if you copy/paste multiple cells.

So the first thing is to decide what you want your code to do if more than one cell is changed at the same time. Depending on that answer, modifications could be made to your code.

BTW, when reporting a code error, it would be better to state ..

a) exactly what the error message is, and

b) what line appears to be causing the error when you click 'Debug'

 

[Sektor]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target.Cells(1, 1), Range("$J:$J")) Is Nothing Then
        If UCase(Target) = "YES" Then
            Pub_DispFile = Range("Main_ShipRef").Offset(Target.Row - 2)
            Pub_CurrentRow = Target.Row - 2
            UFSplit.Show
        End If
    End If
End Sub

 

[Peter_SSs]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target.Cells(1, 1), Range("$J:$J")) Is Nothing Then
        If UCase(Target) = "YES" Then
            Pub_DispFile = Range("Main_ShipRef").Offset(Target.Row - 2)
            Pub_CurrentRow = Target.Row - 2
            UFSplit.Show
        End If
    End If
End Sub

That will cope with deleting row(s) but it doesn't address the other 'multiple cell' issues I raised above:

You would have just the same problem with your code if you selected, say, J3:J4 and pressed the 'Delete' key (try it). Same if you copy/paste multiple cells.

As another example, with your code in place ..
- select J3:J4
- type YES
- confirm with Ctrl+Enter

 

[Zabman]

I see what you mean!

When I select multiple rows, and enter 'yes' into one, it doesn't error out, the yes only enters into one cell, but it sets the variable 'Pub_CurrentRow' completely outta whack which kills the next part of my macro.

The users will be updating one row at a time so this shouldnt be an issue, but you make a good point. I'd love to rule this out but just don't have the experience!

 

[Peter_SSs]

I'm not sure how/if it will affect your other code but a common way to handle multiple cell changes is just to run through the code for each one (in your range of interest) that is changed. Something like this.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Jchanged <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Jchanged = Intersect(Target, Columns("J"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Jchanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Jchanged<br>            <SPAN style="color:#00007F">If</SPAN> UCase(c.Value) = "YES" <SPAN style="color:#00007F">Then</SPAN><br>                Pub_DispFile = Range("Main_ShipRef").Offset(c.Row - 2)<br>                Pub_CurrentRow = c.Row - 2<br>                UFSplit.Show<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


If you just want to do nothing if multiple cells are changed then ..

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    <SPAN style="color:#007F00">'Other code goes here</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>