Workbook_Change stopped working !

krissz

Board Regular
Joined
Feb 21, 2010
Messages
92
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.
Code:
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,506
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,506
It'll probably be useful if you can also post your complete code for Worksheet_Change, thanks
 

krissz

Board Regular
Joined
Feb 21, 2010
Messages
92
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

Board Regular
Joined
Feb 21, 2010
Messages
92
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

Board Regular
Joined
Feb 21, 2010
Messages
92
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,678
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,959
Office Version
2013
Platform
Windows
If you Do a CTRL + G and open the immediates window.
Paste this line of code in the window and press enter

Code:
Application.EnableEvents = True
See if the code now works / runs.
 

krissz

Board Regular
Joined
Feb 21, 2010
Messages
92
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

Board Regular
Joined
Feb 21, 2010
Messages
92
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
 

Forum statistics

Threads
1,077,825
Messages
5,336,595
Members
399,090
Latest member
Mcoca

Some videos you may like

This Week's Hot Topics

Top