CODEWITHSUNDEEP
vbaexcel
Jack
Jack

Reputation: 275

Worksheet Selection Change Not Working

I have the following code which on selection change of a cell searches a separate sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
Dim Finder, ClickRange
Set ClickRange = ThisWorkbook.Sheets("Sheet3").Range("A:A")
If Intersect(Target, ClickRange) Is Nothing Then Exit Sub
Set Finder = ThisWorkbook.Sheets("Sheet4").Range("A:A").Find(Target.Value, LookAt:=xlWhole)
If Finder Is Nothing Then Exit Sub
MsgBox (Finder.Row)
End Sub

However the code isn't working even though in ThisWorkbook I have the following code enabling events

Private Sub Workbook_Open()
Application.EnableEvents = True
End Sub

My sheet names are as follows

Worksheets

Any idea what I might be doing wrong?

Upvotes: 3

Views: 8756

Answers (2)

L42
L42

Reputation: 19727

Try enabling and disabling events like below. This will not only ensure that Events are enabled but will avoid potential issue of calling the event in a loop.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Goto errExit
    Application.EnableEvents = False

    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub

    Dim Finder As Range ', ClickRange
    'Set ClickRange = ThisWorkbook.Sheets("Sheet3").Range("A:A")

    '/* If this code is in Sheet3, you can use below */
    If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
    Set Finder = _
        ThisWorkbook.Sheets("Sheet4").Range("A:A").Find(Target.Value, _
                                                        LookAt:=xlWhole)
    If Finder Is Nothing Then Exit Sub
    MsgBox (Finder.Row)

errExit:
    Application.EnableEvents = True
End Sub

Try this first and let us know what you get. Hope this helps.

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Open Immediate Window and type ?Application.EnableEvents

What do you get? A True or False?

If you get True, all is well but if you get False that means Events are disabled somehow (not because of the selection change event code but maybe because of some other code in the workbook).

To enable it again, Type Application.EnableEvents=True in the Immediate Window.

Now place the following code on Sheet3 Module and see if that works fine for you.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim sws As Worksheet
Dim Finder As Range
Set sws = Sheets("Sheet4")
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target <> "" Then
        Set Finder = sws.Range("A:A").Find(Target.Value, lookat:=xlWhole)
        If Not Finder Is Nothing Then
            MsgBox Finder.Row
        Else
            MsgBox Target.Value & " was not found on " & sws.Name & ".", vbExclamation, "Not Found!"
        End If
    End If
End If
End Sub

Upvotes: 2

Related Questions