Workbook_Change stopped working !

krissz

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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 ?
 
Upvote 0
It'll probably be useful if you can also post your complete code for Worksheet_Change, thanks
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top