VBA protection puzzle

0
votes

An Excel VBA Addin is running the following code in a regular module, modifying another workbook referenced as ActiveWorkBook, which contains a sheet called "Master List".

Whether the code makes it to MsgBox "2" or not depends whether macros are enabled in ActiveWorkBook (not the Addin)! If Macros are not enabled, it prints a "0" and then stops. I know it's made a copy because I see "Master List (2)" as a sheet. But it does not output "1". If Macros are enabled (either with a user permission, or perhaps the ActiveWorkBook is in a trusted folder) the code proceeds through MsgBox "2" and beyond. What's going on??? The sheet Master List does contain events code, if that helps. On the other hand I've turned events off. 

 Application.EnableEvents = False
 With ActiveWorkbook
 .Worksheets("Master List").Unprotect "XXX"
 MsgBox "0"
 .Worksheets("Master List").Copy After:=.Worksheets(1)
 MsgBox "1"
 .ActiveSheet.Name = "OldMasterList"
 MsgBox "2"
1
vbaexcel
A general tip: please see this SO Q+A on how to avoid using ActiveWorkbook, a common cause for errors. - Olle Sjögren
@OlleSjögren thanks for the page, which looks useful. In this case doesn't seem to solve the problem: I can replace "With ActiveWorkBook" with "With Application.WorkBooks(1)" and have the same problem. - user3486991
Still working on this. I know it has nothing to do with events code in Master List. Stripped it out, same problem. - user3486991

1 Answers

0
votes

This worked for me:

Sub Whatever()
Dim wb As Workbook
Set wb = Application.Workbooks("Book 2.xlsm")
 Application.EnableEvents = False
 With wb
 .Worksheets("Overview").Unprotect "XXX"
 MsgBox "0"
 .Worksheets("Overview").Copy After:=.Worksheets(1)
 MsgBox "1"
 .ActiveSheet.Name = "OldMasterList"
 MsgBox "2"
.Worksheets("Overview").Protect "XXX"
MsgBox "3"
 End With
End Sub

Also I'd add in a Protect line, otherwise there'd be no point in unprotecting it again after the first time.