Unable to Show or Hide my VSTO Excel Add-In

Question

I have a ribbon called Ribbon 1

I want my ribbon to show only when a workbook is open. If only the excel application is running and no workbooks are open then I want to hide my ribbon tab. How can I do that?

This is what I tried but it is not hiding the ribbon

Public Class ThisAddIn
    Private Sub Application_WorkbookOpen(ByVal doc As Excel.Workbook) Handles Application.WorkbookOpen
        If Application.Workbooks.Count > 0 Then
            If Globals.Ribbons.Ribbon1.Tab1.Visible = False Then Globals.Ribbons.Ribbon1.Tab1.Visible = True
        End If
    End Sub

    Private Sub Application_WorkbookBeforeClose(ByVal doc As Excel.Workbook, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeClose
        If Application.Workbooks.Count = 1 Then
            If Globals.Ribbons.Ribbon1.Tab1.Visible = True Then Globals.Ribbons.Ribbon1.Tab1.Visible = False
        End If
    End Sub
End Class

I am not getting any error. It is simply not hiding it. I put a break point on If Globals.Ribbons.Ribbon1.Tab1.Visible = True Then Globals.Ribbons.Ribbon1.Tab1.Visible = False. The line executed but the tab didn't hide. I am having a brain freeze! Is this the right way to do what I want?

Answer 1

The following equivalent code in c# works if the ControlIdType of the Ribbon is set to Custom, however it doesn't work if it is set to Office (I assume it is the case for you..). So it seems to me that you find a bug/limitation in the VSTO runtime: it is only possible to change the visibility if the tab is custom (i.e if it is on a new independent tab).

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

    private void Application_WorkbookOpen(Excel.Workbook Wb)
    {
        if (this.Application.Workbooks.Count > 0) {
            if (Globals.Ribbons.Ribbon1.tab1.Visible == false) Globals.Ribbons.Ribbon1.tab1.Visible = true;
        }

    }

    private void Application_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
    {
        if (this.Application.Workbooks.Count == 1)
        {
            if (Globals.Ribbons.Ribbon1.tab1.Visible == true) Globals.Ribbons.Ribbon1.tab1.Visible = false;
        }
    }

Edit: As properly shown in Siddharth Rout's answer, it is not a bug: to hide a tab with the Office configuration, we need to hide all the groups.