0

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"
user3486991
  • 451
  • 6
  • 14
  • A general tip: please see [this SO Q+A](http://stackoverflow.com/q/10714251/1490783) on how to avoid using `ActiveWorkbook`, a common cause for errors. – Olle Sjögren May 22 '15 at 07:22
  • @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 May 22 '15 at 18:01
  • Still working on this. I know it has nothing to do with events code in Master List. Stripped it out, same problem. – user3486991 May 22 '15 at 18:58

1 Answers1

0

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.

Cr1kk0
  • 83
  • 2
  • 14