Workbook_Change stopped working !

[krissz]

I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the user. The user is allowed access to 2 columns, 'Account Code' & 'VAT Rate'. Entry is restricted by Data Validation, the user selecting from lists.
Workbook_Change is used to amend data in other columns depending on the selection.
I did work very well, but having corrected other problems & my errors, Workbook_Change no longer works.
Application.EnableEvents is set true.
Help.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Checks whether Account is changed or VAT is set

    Dim Msg_Ans As Integer
    Dim Msg_Txt As String
    Dim VAT1 As Integer
    Dim VAT2 As Integer
    
    On Error Resume Next
    Target.Range = Range("H:I")
    

    VAT1 = Worksheets("Master").Range("VAT_R1").Value
    VAT2 = Worksheets("Master").Range("VAT_R2").Value
    
    If Target.Column = 8 Then
    ...
    ...

    Range("Q" & ActiveCell.Row).Value = Left(Range("H" & ActiveCell.Row).Value, 3)
    End If

    If Target.Column = 9 Then
        Application.EnableEvents = False
        ....
        ....
            
    End If
    Application.EnableEvents = True

End Sub

 

[StephenCrump]

Workbook_Change no longer works.
Application.EnableEvents is set true.

Does this line actually appear in your code: Target.Range = Range("H:I") ?

This will throw a compile error, suggesting that when you say "no longer works" it means, "isn't being called", as opposed to "is not doing what I want it to".

Is this code in a Sheet Module?

Are the changes being made in that same sheet?

Do you have other event code running that may be setting Application.EnableEvents = False ?

 

[StephenCrump]

It'll probably be useful if you can also post your complete code for Worksheet_Change, thanks

 

[krissz]

Target.Range = Range("H:I") was not in my original code.
As far as I can tell (using a breakpoint), it is never called.
This code is in a sheet module for the applicable sheet where the changes are being made
The other code for setting up this sheet is in Module1 & finishes with "Application.EnableEvents = True "
There is event code running in another sheet.

 

[krissz]

It is suddenly being called. I had to delete " Target.Range = Range("H:I")".
I had earlier rebooted the PC so presume the error was outside the VBA code.
I'd had to repair Excel a few times yesterday.
Thanks for your efforts & advise.

 

[krissz]

More problems with Worksheet_Change. I managed to run the app 2 or 3 times before Worksheet_Change failed; it was not called. It seems to be corrupting either excel or the PC. I have had to repair Excel (full excel) and reboot; still cannot make it work at the moment. Leaving till tomorrow

 

[shg]

Chances are, you have a run-time error so events are not being re-enabled.

Whenever you diasble events, you should have an error handler (On Error Goto ...) to make sure they are.

 

[Michael M]

If you Do a CTRL + G and open the immediates window.
Paste this line of code in the window and press enter

Application.EnableEvents = True

See if the code now works / runs.

 

[krissz]

Thanks for the help & advice. I will try Ctrl-G. The reason why I have not posted the total code is that I did not consider the other code material. There are 3 parts to the code:
1. Load the data, triggered by a button & finishes when the data has been loaded & sorted. The last line is Application.EnableEvents = True
2. Event triggered when Cost Codes & Tax rates are allocated
3. Save the data, triggered by a button when 2 has completed. Saves The workbook, the data as a CSV & closed the workbook.
.
I had been struggling to understand what is happening & find a solution since last Friday, inc the week-end. I only use MrExcel when I am totally stuck.

 

[krissz]

Thanks for the advice & suggestions.
Tried "Application.EnableEvents = True" in the immediate window - no effect
The only place I was disabling events was in the event handler. Added an "On Error Goto ". No effect.
I had noticed a message every time I opened Excel "Sorry, We couldn't open ...". This was an old CSV file; I had been ignoring this but decided to hunt it down.
Finally found it as an Excel Add-in; removed it.
This seems to have fixed it so will continue trying to use my code. See what happens.
Many thanks again, Kris