Workbook_Open after 'Enable Content' strange behavior

[SensualCarrots]

I have two separate workbooks with different scenarios that I am seeing a consistent problem with. Both workbooks have code in the Workbook_Open subroutine.

The first one sets sheet2 to visible, then sheet 1 to very hidden, then selects a range on sheet2, forcing the user to enable macros to see the content. The logic is reversed when exiting and works fine.

The second one run Application.Calculate as the first line of the workbook open because of a formula that returns a #N/A error until the workbook is calculated.

In both scenarios, if I make a new copy of the workbook (or send it to someone else), when initially opening the workbook, and enabling the macros, I get errors. If I save (and have trusted documents turned on) and re-open as to not get the enable content banner, the code executes fine with no error. So I decided to step through the subroutine to see what is going on. Strange enough, when stepping through the first one after enabling the content, the sheets do not respond to the visible/very hidden command until the subroutine completes. This means that when trying to select a range (also tried activating sheet2 after making it visible, this also fails) on sheet2, it fails because sheet2 has not actually been made visible yet. So I saved, closed, reopened, and stepped through the subroutine again, and this time when passing through the worksheet.visible properties, both sheets responded immediately after the line was executed, and the range.select property passed with no problem. Is this something new? I'm using Office 365 64bit. The problem is that we pass documents back and forth quite frequently, and everytime I or someone else opens a new document that isn't trusted, it is riddled with errors. Note that when calling other subroutines from the workbook.open subroutine, the same behavior occurs, and the workbook doesn't respond to the code until the workbook.open subroutine completes.

I've created a file I made from a template, but not sure how to upload it. Below is my code. If you try to recreate this, you'll have to find a way to make the document untrusted (apparently, even if you have trusted documents turned off, but you are the author, you won't get the warning). I did this by uploading it to onedrive then downloading a copy from the site to my desktop. After that I get the warning.

Start by renaming Sheet1 (Code Name and Logical Name) to LockSheet. Then add a new sheet and make sure the Code Name and Logical Name are Sheet1.

The code below goes in the 'ThisWorkbook' object:

Private Sub Workbook_Open()

    LockSheet.Unprotect
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
    
    LockSheet.Visible = xlSheetVeryHidden
    Sheet1.Range("A1").Select
    wbChanged = False

End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    wbChanged = True

End Sub

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

    wbChanged = True

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    wbChanged = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    If wbChanged = True And ThisWorkbook.Saved = False Then
        answer = MsgBox("Want to save your changes to '" & ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation, "Microsoft Excel")
        If answer = vbYes Then
            wbChanged = False
            ElseIf answer = vbNo Then
            noSave = True
            Else
            Cancel = True
            Exit Sub
        End If
        Else
        saveAtEnd = True
    End If
    
    LockSheet.Visible = xlSheetVisible
    LockSheet.Protect
    
    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, ws.CodeName, "LockSheet") = 0 Then
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws
    
    If wbChanged = False Or saveAtEnd = True Then ThisWorkbook.Save
    
    If noSave = True Then ThisWorkbook.Saved = True

End Sub

The below code goes in a Module I called 'PublicVariables'

Option Explicit
Public wbChanged As Boolean
Public ws As Worksheet
Public answer As Variant
Public noSave
Public saveAtEnd

Notice when you first open it, it fails on the Sheet1.Range("A1").Select If you stop the debugger, then save and reopen the file (assuming it becomes a trusted document), the error does not occur.

Any thoughts?

 

[sijpie]

I think it has something to do with saving a copy. I bet that if you just make a copy in Windows Explorer, the copy works fine.

But when you save a copy form the workbook, the sheets are not in the state you want them to be in.

You could add a on_save sub in the workbook module, where you freeze the display, then just as in the on_close sub, swap the hidden nature of the two sheets.
Then the file gets saved.
After this the Workbook_aftersave event gets triggered. So you can write the aftersave sub to swap back the visibility of the two sheets and then unfreeze the display.

 

[SensualCarrots]

Thanks sjpie, but that's not what I'm experiencing. Creating a copy in Windows Explorer makes the newly created document to be untrusted, requiring the clicking of the "Enable Macros" button again, which is when it fails. If you save after enabling the macros and the failure, then re-open the document, it works fine. The error only occurs when Workbook_Open subroutine runs after enabling macros, which is a problem when you send it to someone else. My only solution was to have the users save the document to a particular folder first, and add the entire folder to the trusted documents locations. Either way, it's rather strange behavior.

 

[sijpie]

I have used a similar approach in the past to force enabling macros. Not by hiding / unhiding sheets, but by making a textbox visible before saving (beforeSave) and then when the Workbook_Open runs hiding the textbox. I never heard of problems from the users

The code below is extracted from my real code. You may have to modify to ensure that the activesheet does have the shape on it. protectSheet and unprotectSheet are two subs that do just that.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
            
    ActiveSheet.Shapes("Text_Box_MacroError").Visible = True

End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet

    With ActiveSheet
        unprotectsheet ActiveSheet
        If .Shapes("Text_Box_MacroError").Visible = True Then ' hide the yellow macro enabled error textbox
            .Shapes("Text_Box_MacroError").Visible = False
        End If
        protectsheet ActiveSheet
    End With

End Sub

 

[RoryA]

You need to call the code from the workbook_activate event if the workbook opens in a protected window: https://support.microsoft.com/en-us...il-from-workbookopen-event-when-exiting-prote

 

[sijpie]

Ah, that explains why a piece of code I have to do something similar uses the workbook_activate. I must have figured that out then, long time ago... Should have made a comment in the code.

 

[SensualCarrots]

Thank you both. I simply created a public variable that is set to true whenever the workbook opens, and reference that in the workbook_activate sub to determine whether the code should run. I put the range.select command in that if statement, and all is working perfectly. Thanks again!