Cfrgtkky

I am having difficulty with the load behavior of an Excel Ribbon Add-In developed as a VSTO solution.
The Add-In serves as an Excel VSTO Ribbon control that has several tabs, each tab's visibility is set to false by default and made visible by the workbook's Add-In instantiation.

For reasons that are unclear to me allowing the ClickOnce install to set the LoadBehavior to 16 (then a subsequent 9) is problematic. The initial value of 16 will load the workbook ribbon tab properly but after closing and reopening the same workbook the ribbon tab is not displayed.

However if another workbook is opened with the same Add-In, both tabs appear in each of the workbooks.

The LoadBehavior of "0" works as intended in any case and is the reason for the posted question.

Here is the VBA code for the Add-In instantiation

'This loads the Ribbon Addin

Private Function LoadAddIn() As Boolean

    On Error GoTo Err_LoadAddIn

    Dim msg As String

    Dim m_addIn As COMAddIn

    Dim m_automationObject As Object

    Dim m_sWorkbookKey As String    



    msg = "Unable to load the PITA AddIn, please contact PITA support"



    'Load the Excel Addin

    Set m_addIn = Application.COMAddIns("PITA Ribbon")

    'Connect the COM Add-In to the current workbook

    m_addIn.Connect = True



    'Set a reference to the utility class that the COM Add-In references

    m_automationObject = m_addIn.Object

    'If it is nothing then the Add-In is in a bad state

    If m_automationObject Is Nothing Then

        msg = "Error loading the PITA AddIn, please contact PITA support"

        GoTo Err_LoadAddIn

    Else

        'Set the service type of the Add-In (currently only SQLServer)

        m_automationObject.SetDataConnection "SQLServer"

        'Set the workbook key - this is the name of the analyzer

        m_sWorkbookKey = Worksheets("SheetX").Range("A10")



        'Set the ribbon tab's visibility relative to the current workbook

        m_automationObject.SetTabVisibility m_sWorkbookKey, True



        'If the connection to the datasource is successful

        'Populate the Tab's List Controls with the values from SQL Server

        If m_automationObject.Connected = True Then

            m_automationObject.SetTabDefaults m_sWorkbookKey

        End If

    End If



    LoadAddIn = True

    Exit Function



Err_LoadAddIn:

    MsgBox msg, vbCritical, "AddIn load error"

    LoadAddIn = False

End Function

I have tried altering the VSTO manifest file in the customization section below to "0" but the ClickOnce installer errors out when attempting an installation with this modification.

    <vstov4:customization>

      <vstov4:appAddIn application="Excel" loadBehavior="0" keyName="PITA Ribbon">

        <vstov4:friendlyName>PITA Ribbon</vstov4:friendlyName>

        <vstov4:description>PITA Ribbon</vstov4:description>

        <vstov4.1:ribbonTypes xmlns:vstov4.1="urn:schemas-microsoft-com:vsto.v4.1">

          <vstov4.1:ribbonType name="PITAAddIn.PITARibbon, PITA Ribbon, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />

        </vstov4.1:ribbonTypes>

      </vstov4:appAddIn>

    </vstov4:customization>

Is there a way to have a ClickOnce installation set the default load behavior to "0"?

Any guidance on how to do this is greatly appreciated!

You need to re-sign the deployment any time you change the manifest directly after you publish it.

MSDN documentation

One of my answers that has a powershell script that automates this

The question posed by Chris in the comments section in the initial post identifies the core issue (i.e. Did you re-sign the deployment after changing loadBehavior to 0?)

Once the manifest file is modified for the Add-In, one must re-sign the manifest and assembly.

Instructions for modifying the VSTO deployment properties are addressed here

Instructions for re-signing assemblies and deployment manifests are addressed here

Generating self-signed certificates can be accomplished with PowerShell, this is the link that I used to create a code signing certificate for a test environment.

My steps were as follows:

• Generate a new code signing certificate using PowerShell. komsky provides a great command line example.


• After publishing the VSTO Add-In make modifications to the Add-In assembly manifest by LoadBehavior to 0


• Use MAGE.EXE to sign the manifest mage.exe -sign "PITA Ribbon.dll.manifest -CertFile "C:Certificatescert.pfx -Password ******** -a sha256RSA
  


• Use MAGE.EXE to update the VSTO deployment manifest mage.exe -update "C:PitaDeployPita Ribbon.vsto" -appmanifiest "Pita Ribbon.dll.manifest" -CertFile "C:Certificatescert.pfx -Password ******* -a sha256RSA
  

After performing these steps the VSTO installed with no issues.