worksheet.change event not firing for most events

[danners430]

Hey guys

I've got a troublesome spreadsheet where strange things happen when the worksheet.change event is supposed to fire...
The worksheet has a large table in it, using Excel's built in table functionality.
The event, of course, should always fire when a cell is altered - but in this sheet, it only fires under the following circumstances:
1. When a value outside of the main table is deleted
2. When a value in column 10 is deleted
3. When a value of the correct length is entered into column 10 - except here it doesn't show the macro running when breaks are added to the code - it just does it.

Unfortunately, I can't upload the spreadsheet, but I can upload the code (albeit very slightly edited). here it is:

Option ExplicitDim ChangeDis As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub


If ChangeDis = True Then Exit Sub
ChangeDis = True


If Target.Column = 10 Then
    If Target.Cells <> "" And Len(Target.Cells.Value) = 3 And IsNumeric(Target.Cells.Value) = True Then
        Cells(Target.Row + 1, 1).Activate
        Cells(Target.Row, 10) = "XXX-1000004" & Cells(Target.Row, 10).Value
    End If


ElseIf Target.Column = 2 Then
    If Target.Cells <> "" Then Cells(Target.Row, 4).Activate


End If


ChangeDis = False


End Sub

In module 1 (conditional formatting functions):

Option Explicit

Public Function Difference(Row As Integer)


Application.Volatile


If Cells(Row, 2) <> Cells(Row, 3) Then
    Difference = "Limit Error"


ElseIf Cells(Row, 8) = "" Then
    Difference = "No Values"


ElseIf Cells(Row, 8) = Cells(Row, 2) Or Cells(Row, 8) = Cells(Row, 3) Then
Difference = 0


ElseIf IsNumeric(Cells(Row, 8)) = True Then
Difference = Cells(Row, 8).Value - Cells(Row, 2).Value


Else
Difference = "Error"


End If


End Function


Public Function CheckNumeracy(Value) As Boolean


Application.Volatile


If IsNumeric(Value) = True Then
CheckNumeracy = True
Else
CheckNumeracy = False
End If


End Function

I've tried all the usual suspects,

application.enableevents = true
application.screenupdating = true
application.dataentrymode = false

At this point I'm at a loss... anyone got any ideas?

 

[jmacleary]

Hi there

You appear to be using ChangeDis to work round recursive calls to the change event. If thats the case, I would suggest the following:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub


Application.EnableEvents=False


If Target.Column = 10 Then
    If Target.Cells <> "" And Len(Target.Cells.Value) = 3 And IsNumeric(Target.Cells.Value) = True Then
        Cells(Target.Row + 1, 1).Activate
        Cells(Target.Row, 10) = "XXX-1000004" & Cells(Target.Row, 10).Value
    End If


ElseIf Target.Column = 2 Then
    If Target.Cells <> "" Then Cells(Target.Row, 4).Activate


End If


Application.EnableEvents=True


End Sub

 

[danners430]

I tried that, it didn't work I'm afraid... I did, however, find the cause of the problem - By removing the function "CheckNumeracy" from conditional formatting, the problem disappeared... Also, removing the table and reformatting it as a range also fixed the issue... Bug with Excel tables?