none
Office 2010, activating sheet fails in Workbook_Open after going out of protected view RRS feed

  • Question

  • xls file opened from intranet in protected view

    It has a procedure triggered by Workbook_Open event

    First line of code activates one sheet by its codename

    It fails just after user clicks "edit anyway" to go out of protected view but it works fine when is launched again from run macro form (ALT+F8)

    Is it a bug in Office? Is there any workaround?


    • Edited by krzcho Monday, August 8, 2011 2:18 PM subject clarification
    Monday, August 8, 2011 10:54 AM

All replies

  • The sheet to open has the workbook name reference as well? Maybe it's failing because the code is trying to open the sheet in the active workbook (which may not be the case if the user has another Excel session opened, for instance).

    If that's the case, try to change

    Sheets(<sheetName>).Activate

    for

    Workbooks(<workBookName>).Sheets(<sheetName>).Activate


    Tiago Cardoso VB / VBA Analyst
    Monday, August 8, 2011 1:09 PM
  • the code looks like that:

    sheetCodeName.Activate

    I assume (and it work fine in Office 2003) that sheet codenames are defaulted to ThisWorkbook (and as I said - it only happens during the switch from protected view, it runs fine afterwards)

    Below is a link to the file which shows what is now broken

    https://docs.google.com/leaf?id=0B8Pbr6Jr1eaCODVlOGNiM2UtODY3NC00YjE4LWFmOTctYWE5YzZkYzg2NTFk&hl=pl


    even changing the code to

    ThisWorkbook.Sheets("Sheet2").Activate

    does not help and brings the same error

    https://docs.google.com/leaf?id=0B8Pbr6Jr1eaCYmZhNTllZTctYmJhZC00M2ViLTg1YmMtZWIyOTI3NzA1YTg0&hl=pl

    I need to check whether it is only a problem with activating the sheet...

    Monday, August 8, 2011 1:22 PM
  • And what's the error message you got?
    Tiago Cardoso VB / VBA Analyst
    Monday, August 8, 2011 2:00 PM
  • Run-time error '1004': Method 'Activate' of object '_Worksheet' failed

    hopefully it is only a problem with Activate and other references to sheet work fine

    Monday, August 8, 2011 2:16 PM
  • I have no access to gdocs here... hopefully some other folks around might be able to check this out and help you further.

    One thing: The code that's calling the sheet activation is within a proper module or within a sheet object? I've seen cases where the code was halting because it wasn't being called from a module.

    Try to add a module and move the function that triggers this action there, if possible.


    Tiago Cardoso VB / VBA Analyst
    Monday, August 8, 2011 2:33 PM
  • it is within a module

    hmm, i could try moving it to the worksheet's code...

    no, no luck there as well

    In general i treat this as a minor office 2010 bug (version 14.0.5128.5000 32-bit)

    Monday, August 8, 2011 2:42 PM
  • Not sure of this but I am thinking that Protected View is doing what it is supposed to do and blocking the code. Have a look at the following site and you might pick up on something that answers your question.

    http://blogs.technet.com/b/office2010/archive/2009/08/13/protected-view-in-office-2010.aspx


    Regards, OssieMac
    Tuesday, August 9, 2011 1:44 AM
  • OssieMac: for sure protected view blocks macros but what i report is an error which happens AFTER USER GOES OUT OF IT (by pressing Edit Anyway in file options)
    Tuesday, August 9, 2011 6:29 AM
  • One additional feature not working during "Edit Anyway": Application.StatusBar is not accessible
    Tuesday, August 23, 2011 5:58 PM
  • xls file opened from intranet in protected view

    It has a procedure triggered by Workbook_Open event

    First line of code activates one sheet by its codename

    It fails just after user clicks "edit anyway" to go out of protected view but it works fine when is launched again from run macro form (ALT+F8)

    Is it a bug in Office? Is there any workaround?



    When Excel is still opening a workbook, the "state of the workbook," for want of a better term, is not "completely defined," again, for want of a better term. {grin}

    I find that things like activating objects / changing the state of the environment will fail, sometimes siliently, in the workbook_open event procedure.

    What will work -- hopefully -- is to schedule, from within the workbook_open procedure, a regular subroutine in a standard module for execution after zero seconds.

    So, in workbook_open:

    applicatin.ontime now(),"RealWBOpen"

    then in a standard module:

    sub RealWBOpen()
        'your real workbook open code here
        end sub

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Wednesday, August 24, 2011 4:05 PM
  • I was thinking about it earlier but as you have provided me a code for scheduling it - i have tried - unfortunately launching Application.OnTime fails as well...

    Thursday, August 25, 2011 8:27 AM
  • I've got exactly the same problem here. I try to activate a worksheet with VBA on Workbook_Open and the code fails with the same error as you when the document is opened from the network, in protected view mode.

     

    Did you manage to find a solution ?

    Thanks.

    Friday, September 16, 2011 1:02 PM
  • Unfortunately not. My workaround was to save workbook with proper worksheet visible (using OnClose event). Hopefully it was not crucial to have Activate working on start-up.
    Friday, September 16, 2011 4:38 PM
  • krzcho, 

    Try using the Workbook_Activate event instead. I was having the exact same issue and this seemed to fix it. The only downside to this strategy is that your open-code will be executed each time the Workbook_Activate event is triggered. However, please consider the following:

    Private bActivated As Boolean

    Private Sub Workbook_Open()
        bActivated = False
    End Sub

    Private Sub Workbook_Activate()
        If Not bActivated Then Call SetExcelWin_All
        bActivated = True
    End Sub

    • Proposed as answer by LEVELTWO Wednesday, July 18, 2012 5:14 PM
    Wednesday, July 18, 2012 5:13 PM

  • I had exactly the same problem. LEVELTWO's solution worked perfectly. I just moved the code from the Workbook_Open() event to the Workbook_Activate() event. And via a public flag set to false inside the Workbook_Open() event, I managed to control whether the Workbook_Activate() event is triggered or not.
    Thank you very much!



    • Edited by Mits O Wednesday, May 18, 2016 9:03 AM
    Wednesday, May 18, 2016 9:01 AM