none
Workbook Open event not called with Excel 2016 RRS feed

  • Question

  • Hi,

    I am developing a VSTO Add-In where I need to hide/show the ribbon tab based on some condition.

    Env. details:

    Excel 2016

    Visual Studio 2015.

    .Net Framework : 4.0 (Issue occuring with 4.5.2)

    Below is the code which is working with Excel 2013, but not with Excel 2016.

    private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                this.Application.WorkbookOpen += Application_WorkbookOpen;
            }

            private void Application_WorkbookOpen(Excel.Workbook Wb)
            {
                Globals.Ribbons.Ribbon1.tab1.Visible = false;
            }

    My ribbon has one tab named tab1. tab1 has one button inside a group. tab1 visibility is set to true (in designer)

    The above code doesn't hide the tab.

    If I set the tab1 visibility to false( in designer), and code the above lines to set it to true, even that doesn't work.

    Is it a bug with Excel 2016 interop?


    • Edited by Gaurav Dari Tuesday, January 2, 2018 5:33 AM Changing title to suit the topic under discussion
    Tuesday, December 26, 2017 1:47 PM

All replies

  • To add,

    If I launch excel process as an administrator, it works fine.

    But if I double click excel workbook, issue is seen.

    Tuesday, December 26, 2017 1:48 PM
  • Hello Gaurav Dari,

    As far as I know, we could not hide/show tab at runtime. I tried your code for testing in Excel. I failed to hide/show the tab even I run excel as administrator. 

    I'm wondering if i miss-understand anything and would you please share a simply project which could reproduce your issue and your repro steps so we could try  to reproduce this issue?

    You could share the project via One Drive and then put link address here. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 27, 2017 10:22 AM
  • Thanks Terry.

    Below is the OneDrive link:

    https://1drv.ms/f/s!ArsFKRT3ncwDgjsDrrLegWYYv6B5

    Another point to consider is this piece of code worked with Excel 2013 & Excel 2010.

    (Built with VS 2012).

    Wednesday, December 27, 2017 10:51 AM
  • Hello Gaurav Dari,

    Thanks for sharing the project and it helps a lot. After comparing with your project, I found that i set the tab as a built-in tab TabAddIns and it should be a custom tab. 

    I tried to test your project with Excel 2016. I failed to reproduce your issue again. This time, it could work to hide the tab whether launch Excel as Administrator or not. I tried to test it in two pc and I get the same result.

    I would suggest you unload all other add-ins in case of conflict and try to repair your office. You could also try to test this in another pc with Excel.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 28, 2017 6:18 AM
  • Hi Gaurav Dari,

    I try to download your project and try to test it.

    I find that there is some issue in the code.

    to hide the tab you need to replace your line of with line below.

     Globals.Ribbons.Ribbon1.Tabs[0].Visible = false;

    other thing I find that , Workbook_open event not get called.

    so event not get called and tab not get hidden.

    I try to make several test but event did not get fired.

    as a work around I try to use Application.WorkbookActivate Event and it work well.

    example:

      private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                
                Application.WorkbookActivate += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);
            }
            void Application_WorkbookActivate(Microsoft.Office.Interop.Excel.Workbook Wb)
            {
                Globals.Ribbons.Ribbon1.Tabs[0].Visible = false;
            }

    so now when you run it you will find that your ribbon tab is not visible any more.

    further I find that if workbook opened as protected then "Workbook_Open" event not get fired.

    in that case you need to call "Protected View Window Open" Event.

    Reference:

    Workbook open event not firing when opening file from SharePoint on Excel 2010

    Regards

    Deepak



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 28, 2017 6:21 AM
    Moderator
  • Thanks Deepak. I will try it out.

    Any reason why this changed with Excel 2016? With Excel 2013, this was not the issue.

    (about open event not getting fired)

    Thursday, December 28, 2017 9:15 AM
  • 1. I need some code to be executed only when workbook is opened.

    With activated event, I will be difficult to track which workbook is opened or activated.

    2. I have another piece of code to be executed only when sheet is activated and not opened.

    (In case there are multiple workbooks open, and user switches between workbooks).

    Thursday, December 28, 2017 10:08 AM
  • I tried to put the activated event instead of open event, even activated doesn't get fired.
    Friday, December 29, 2017 4:43 AM
  • I tried to put the activated event instead of open event, even activated doesn't get fired. at my end.
    Friday, December 29, 2017 4:43 AM
  • Hi Gaurav,

    Could you share us detail steps to reproduce your issue with your provided project?

    I made a test with steps below:

    1. Launch your project in VS 2015 and Excel 2016

    2. Excel Window-> Recent-> Select One Workbook to open->tab1 show and hide

    3. If I put a breakpoint on “Globals.Ribbons.Ribbon1.tab1.Visible = false;”, it will be hit.

    4. Double Click one excel file on desktop, tab1 will be hidden on this Excel windows too.

    Is there any difference between yours?

    Before making any test, I suggest you uncheck all the other addins.

    To check whether it is related with your Excel, I suggest you make a test with your project on other Excel 2016 computer.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 29, 2017 8:24 AM
  • Hi,

    There is a conflict in what you are suggesting and what Deepak mentioned.

    As per Deepak, the workbookOpen event is not getting fired and hence the code to hide/show will not be executed; which I agree with.

    I tried this with three different VMs and same issue.

    Customers have also complained about same.

    Now, as per Deepak, the activated event should get fired, but even that doesn't work for me.

    Questions that arise: What made MS to stop firing these events. What is the alternative?

    Friday, December 29, 2017 8:42 AM
  • Hi Gaurav,

    Do you mean if you put a breakpoint on "Globals.Ribbons.Ribbon1.tab1.Visible = false;" and follow my above steps, it will not be hit?

    If so, what is your Excel version? Could you record your steps and share us?

    This issue does not exist at my side with Excel 2016 MSO (16.0.8827.2082) 64 BIT.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 29, 2017 8:50 AM
  • If you execute the code putting breakpoint. Then open workbook, it works.

    But if you open the same workbook through double click workbook, it doesn't.

    (Without VS)

    Try it out. If you are are still not able to reproduce, it will share steps.


    • Edited by Gaurav Dari Friday, December 29, 2017 9:35 AM
    Friday, December 29, 2017 9:34 AM
  • Hi Gaurav,

    >>But if you open the same workbook through double click workbook, it doesn't. (Without VS)

    What do you mean by without VS?

    I stop VS debugging, and then double click the same workbook, it will show Excel Window without "tab1".

    I think you could add code below to check whether the event is fired while VS is stopped.

            private void Application_WorkbookOpen(Excel.Workbook Wb)
            {
                Globals.Ribbons.Ribbon1.tab1.Visible = false;
                MessageBox.Show("Application_WorkbookOpen");
            }

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 29, 2017 9:40 AM
  • I have shared recording @https://1drv.ms/u/s!ArsFKRT3ncwDglDK7BO-76K7RAev
    Friday, December 29, 2017 9:57 AM
  • Hi Gaurav,

    Thanks for sharing the recording, and your issue is much clear now. Do you mean the Application_WorkbookOpen did not fire after you close the first worbook? If so, it is expected behavior. This is caused that the VS Debugging is stopped after closing the workbook which is launched by VS.

    If you need it work all the time, you need to publish and install this addin.

    For a temporary way without installing, you could build your solution before opening the workbook.

    Right Click Project->Build or ReBuild-> After it built correctly-> Open the workbook to check the message.

    Best Regards,

    Tao Zhou 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 3, 2018 7:12 AM