"Worksheet_Change, If not intersect" question

[PATSYS]

Hi all,

I have a worksheet_Change event in my sheet.

I would like that if values are being entered in either J3 or J5 and if the rightmost value is not "\" then the code should add the "\" at the end of either J3 or J5 (depending on which one is being updated.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("J3,J5")) Is Nothing Then

         ???
         ???
                
    End If


End Sub

Thanks

 

[MickG]

Try this:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("J3", "J5")) Is Nothing Then Exit Sub
    If Not Right(Target, 1) = "\" Then
    Target = Target & "\"
       End If
End Sub

Regards Mick

 

[PATSYS]

Thanks Mick, but it does not seem to work.

It is just not doing anything.

 

[Norie]

What's the question?

Application.EnableEvents = False
If Right(Target.Value,1)<>"\" Then Target.Value = Target.Value  & "\"
Application.EnableEvents = True

 

[MickG]

Hi, Although this code worked for me there is a slight syntax error Range("J3","J5") should read Range("J3,J5").
The code should work if you click either "J3" or "J5" and the last character of the text is not"\" then the code will insert in, is that what you wanted ??
Regards Mick

 

[PATSYS]

What's the question?

Application.EnableEvents = False
If Right(Target.Value,1)<>"\" Then Target.Value = Target.Value  & "\"
Application.EnableEvents = True

Hi Norie,

Thanks for your post. I inserted the lines you suggested, my code now looks like:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

    If Not Intersect(Target, Range("J3, J5")) Is Nothing Then Exit Sub

        If Right(Target.Value, 1) <> "\" Then
            Target.Value = Target.Value & "\"
        End If
                
    'End If
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

But it does not work...

Any idea why?

Thanks

 

[PATSYS]

Hi, Although this code worked for me there is a slight syntax error Range("J3","J5") should read Range("J3,J5").
The code should work if you click either "J3" or "J5" and the last character of the text is not"\" then the code will insert in, is that what you wanted ??
Regards Mick

Hi Mick,

I tried it using "J3, J5" (see my last post) and using Worksheet_change event instead of Worksheet_selectionChange. But for some reasons it does not work...it simply does not do anything.

 

[MickG]

Hi, If your using the change event it could be the code is set to Application.EnableEvents = False. You could try Running a seperate bit of code with this line set to "True", to unblock it.
I would have thought Selection change event would be better, then you get a result by "Selection Change" rather than "Changing" a value.
Regards Mick

 

[wsjackman]

you keep saying, "it does nothing" , do you have macros enabled?

I get the feeling you want to remove any "\" from the other cell

try

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
If Intersect(Target, Range("J3,J5")) Is Nothing Then GoTo 100
If Not Intersect(Target, Range("J3")) Is Nothing Then
If Right(Range("J3"), 1) = "\" Then
GoTo 100
Else: Range("J3").Value = Range("J3").Value & "\"
If Right(Range("J5"), 1) = "\" Then Range("J5").= Left(Range("J5").Value, Len(Range("J5")) - 1)
End If
Else
If Right(Range("J5"), 1) = "\" Then
GoTo 100
Else: Range("J5").Value = Range("J5").Value & "\"
If Right(Range("J3"), 1) = "\" Then Range("J3") = Left(Range("J3"), Len(Range("J3")) - 1)
End If
End If
100
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Norie]

Where did you insert the lines I suggested?

I don't see this anywhere.

If Right(Target.Value,1)<>"\" Then Target.Value = Target.Value  & "\"

Try selecting the sub header and pressing F9.

This creates a breakpoint in the code.

Now goto the worksheet and type something in a cell.

Is the code triggered?

If it isn't there are at least 2 possibilities:

1 The code isn't in the worksheet module, or is in the wrong worksheet module.

2 Events are disabled as Mike has suggested.