Excel 2010 vba EnableEvent in Open event not working when code inserted

Question

As I attempt to improve efficiency in a complex Excel model with over 100 macros, I wanted to ensure events were enabled after a module ran to convert a template with formulas to a values only file. Using the code below, I expected that other event code inserted into an otherwise VBA-less Excel file would work, but the Open event code below does not enable events. I've done a search and see that EnableEvents is tricky. My goal is that use of some other code that I found online to "Optimize" or speed up the running of codes would be more widely possible. It includes EnableEvent language (turn it off at the beginning and on at the end of code).

Here is the code I have adapted to get the "VALUES" version of the template to enable events in general, once I navigate to another tab and make a selection change with a sub called Private Sub Worksheet_SelectionChange(ByVal Target As Range).

Sub AddSheetCodeToAuto_Open()
' To turn on events
Dim Startline As Long, HowManyLines As Long
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
    Startline = 1
    HowManyLines = .CountOfLines
    .DeleteLines Startline, HowManyLines
.InsertLines 1, "Private Sub Workbook_Open()"
.InsertLines 2, vbNewLine
.InsertLines 3, "Sheets(Sheet7).Select
.InsertLines 4, "Application.EnableEvents = True"
.InsertLines 5, "End Sub"
End With
End Sub

When I use the Immediate window and step through code, the Application.EnableEvents value is always FALSE after opening the file in question, but given that the event handler for the open workbook event is as seen above, why will events not turn on? Thank you in advance for your expertise.

That code should go in the ThisWorkbook code module, not a worksheet module. — Tim Williams
– Tim Williams, Commented Oct 23, 2018 at 20:04
So true, I forgot to mention that the Workbook_Open code was moved into that module. Here's the revised code: With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule What is a bother is that if I turn off events at the end of my "convert to values" procedure, the auto open event handler is ineffective in enabling events. — Chris Mishler
– Chris Mishler, Commented Oct 23, 2018 at 21:10
Typically you'd manage this by including error handling in any procedure where you turn off events, to ensure your code won't crash and leave events turned off. — Tim Williams
– Tim Williams, Commented Oct 23, 2018 at 21:21

Ahmed AU · Accepted Answer · 2018-10-24 09:57:33Z

0

I think Line 3 is encountering an error. Changing the 3 will do the trick

.InsertLines 3, "Sheets(" & Chr(34) & "Sheet7" & Chr(34) & ").Select"

also it depends on existence of "Sheet7".

But still best solution is the suggestion by @Tim Williams

edited Oct 24, 2018 at 9:57

answered Oct 24, 2018 at 5:59

Ahmed AU

2,7772 gold badges9 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Mishler Over a year ago

You are correct in that line 3 needed to refer to the specific sheet name, not the Excel sheet # (Sheet7). I did make that change. I am not using "Sheet7" as a sheetname on the sheet tab.

Chris Mishler Over a year ago

As Tim recommends, I have been considering how to use error handling to turn on events, but I do not receive an error message that the auto open event did not run or work properly. It just does not enable events. I will think harder how I can arrange the components to ensure events are enabled after a) removing VBA code (except that which reinserts the desired event code for navigation to other sheets), b) saving the file as a "VALUES" one, and c) reopening the VALUES file, where the Open event would turn on events and leave them on.

Collectives™ on Stack Overflow

Excel 2010 vba EnableEvent in Open event not working when code inserted

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related