This content elaborates on the VBA Worksheet Change not working regarding possible reasons and their remedial approaches.
Just think about your task, you require to make changes or input data frequently. Like you want to recalculate every time changes take place. Unfortunately, Excel doesn’t update itself automatically. Despite this fact, Excel facilitates a VBA Change procedure that helps to recalculate after the dataset changes every time. The Change procedure in the VBA Worksheet event is one of the most widely used procedures.
VBA Worksheet Change Is Not Working in Excel: 3 Effective Fixes
We can use Worksheet_Change event based on multiple cells. But we might face problems dealing with this Change event. There are 3 frequent possible reasons for the VBA Worksheet Change event not working in Macro. To highlight the shortcomings as well as their remedy, we are using the following dataset containing Order ID, Order Date, Region, Sales Person, and Sales Amount. As you can observe in the image below, we applied the RANDBETWEEN function to provide a clear idea.
1. Check Whether You Put Private Sub-Procedure
Often, many of us make a common mistake when we write VBA code declaring Sub procedures in the Module. In contrast, we must use the Private Sub procedure to enable Worksheet Change events as the Sub procedure of the module only works once we hit the Run icon. However, it is accessible to all the worksheets in the workbook.
Correct Procedure:
- Initially, select the Event worksheet >> Then select worksheet from the Object field >> Next choose Change from the Procedure field >> Finally insert the VBA code to execute.
- Therefore, the outcome after a change is as follows.
2. Write VBA Code in Sheet Instead of Module
To enable the Change procedure, we must use not only the Private Sub procedure but also notice if it is inserted in the worksheet private module. The Private Sub procedure does not work when we hit the Run icon or press F5 key writing in the General object module.
Correction:
One must insert the VBA code in the private worksheet module to enable the Change event instead of the General object module.
3. Unblock File to Enable VBA Worksheet Change Event
The Security risk alert is quite common while working with a spreadsheet file. Eventually, it appears when we download the file from the internet or just import the file from another user. Likewise, we opened a downloaded file and ran a macro event that leads to an error saying The macros in this Project are disabled.
Solution:
- In the beginning, go to the File directory >> Then right-click on the file >> Next, select the Properties option from the Context menu >> Finally check the Unblock from the Properties dialog and hit the Apply command.
- After Unblocking the Spreadsheet file from the Properties, now it works perfectly.
Things to Remember
- In the older versions, users may require using the EnableEvents properties inside a worksheet event to turn or off events for short VBA code.
- Use Private Sub in the Worksheet module to implement the Change Procedure in Excel.
Frequently Asked Questions
Q1. How do I Enable or disable worksheet events in VBA?
Application.EnableEvents=TRUE for enabling and FALSE for disabling worksheet events.
Q2. How to set a Change event in an Excel worksheet?
First, go to the VBA Worksheet module, select the Worksheet from the Object field >> Then choose Change from the Procedure field.
Q3. Can I recalculate a Pivot Table using VBA Change Worksheet event?
Yes, using the ThisWorksheet.Calculate properties, under the Change worksheet event, we can recalculate the Pivot Table.
Download Practice Workbook
To practice, please download the Excel Workbook file from the link below.
Conclusion
Throughout the content, we delineated 3 possible reasons and solutions regarding the VBA worksheet Change not working. We hope now you will be able to figure out findings while using Worksheet Change event in Excel. Any suggestions, as well as queries, are appreciated. Leave your valuable insights in the comment section. For related articles and knowledge, don’t forget to visit our site.
