Worksheet_Change selectively firing

[wesborland1234]

Hello,

I have a workbook, with one sheet, and some code running in the worksheet_change event. We have three copies of the worksheet on three different computers. On two of them, it works fine. On the third, when you open it up, you can click somewhere, and the event will fire. The next time you click somewhere nothing happens. And now, nothing will happen on any change, until I close and reopen the file. Here's where it gets wierder. I click on a cell, wait for the event to fire, and it doesn't. Then, when I open up Visual Basic, the event I was waiting for does fire.

The sheet name is correct, macros are enabled. I tried setting Application.EventsEnabled = true, at the beginning and end of macro, and this does not change anything.

Any ideas on what is going on here?

Also, when we attempt to exit Excel on the affected computer, nothing happens, so we have to go into task manager and close it that way.

Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    If Target.Cells.Count = 1 Then
        If Target.Column = 1 Then
            Dim Shex As Object
            Dim tgtFile As String
            Dim fileName As String
            fileName = Cells(Target.Row, Target.Column).Value & ".tif"
            Set Shex = CreateObject("Shell.Application")
            tgtFile = ThisWorkbook.Path & "\" & fileName
            Shex.Open (tgtFile)
        End If
    End If
End Sub

 

[Leith Ross]

Hello wesborland1234,

Your post addresses the Worksheet_Change() event as the problem but you have posted code for the Worksheet_SelectionChange() event. These are two separate events.

You did not give any details about the machine and the operating system on which the code fails.

My best guess based on the behaviour you mention would be the Application Events are disabled when the macro is called but are not restored. The only way to be sure would be to examine all of the macro code in your workbook. But this doesn't explain why it affects only one of the three computers.

To find the problem I would need to test a copy of your workbook. If you can post a copy on a file sharing site or email me a copy then I can find the problem more easily.

 

[wesborland1234]

I meant SelectionChange. Application Events were my first thought, so I enabled them at the end of the macro, and this did nothing. I also tried setting Shex = Nothing, both at the beginning of code, and at the end, and this did nothing.

I posted a copy of worksheet (with sensitive information deleted) here: http://batesplus.com/temp/example.xlsm

thank you for your help

 

[wesborland1234]

I think I've found a solution. For some reason, when the user double clicks the cell (as opposed to a single left click), everything works as expected. I'm not sure why this would happen in one version of Excel and not another, but it seems to have solved my problem for now.

 

[Leith Ross]

Hello wesborland,

Try this version of the macro. The events are disabled at the beginning of the macro and re-enabled before exiting the macro. I added some code to check if the cell is empty before calling the Shell to open the file. This is just my preference. Why execute the other code if the file name is blank?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Shex     As Object
    Dim tgtFile  As String
    Dim fileName As String
    
        Application.EnableEvents = False
        
        If Target.Cells.Count = 1 Then
            If Target.Column = 1 Then
                If Trim(Target) <> "" Then
                    fileName = Cells(Target.Row, Target.Column).Value & ".tif"
                    Set Shex = CreateObject("Shell.Application")
                    tgtFile = ThisWorkbook.Path & "\" & fileName
                    Shex.Open (tgtFile)
                End If
            End If
        End If
        
        Application.EnableEvents = True
End Sub

 

[Norie]

If double clicking works why not change to using the BeforeDoubleClick event?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Shex As Object
Dim tgtFile As String
Dim fileName As String

    If Target.Column = 1 Then
        Cancel = True
        fileName = Cells(Target.Row, Target.Column).Value & ".tif"
        Set Shex = CreateObject("Shell.Application")
        tgtFile = ThisWorkbook.Path & "\" & fileName
        Shex.Open (tgtFile)
    End If

End Sub

 

[Fluff]

For some reason, when the user double clicks the cell (as opposed to a single left click),

Double check the code on the pc that is "odd". It sound as if that is a Worksheet_BeforeDoubleClick event rather than a Worksheet_SelectionChange event.
I would also recommend against using VBA keywords, such as filename, for your variables as this can lead to problems.