CODEWITHSUNDEEP
excelvba
Chris Mishler
Chris Mishler

Reputation: 11

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

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.

Upvotes: 1

Views: 222

Answers (1)

Ahmed AU
Ahmed AU

Reputation: 2777

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

Upvotes: 0

Related Questions