CODEWITHSUNDEEP
c#excelvbavstoexcel-addins
Tsiry Rakotonirina
Tsiry Rakotonirina

Reputation: 731

Custom Task Pane: How can I intercept Excel worksheets' visibility change from the Addin?

DataGridView populated with Worksheets & its visibility Status:

I have a Custom Task Pane for my Excel Addins with a DataGridView I populate from my open WorkBook:

  1. Its content are all the Sheets of the open Workbook.
  2. Rows are visible or not based on the Worksheets visibility Status (visible/hidden/SuperHidden).

Intercepting Worksheets Visibility change from the Addin?

I have operations/tasks happening in the Excel Workbook that the User does that can show/hide worksheets based on what s/he's doing. So,

Your suggestions/recommendations regarding this is greatly appreciated. Please feel free to let me know should you ever need further information, I'll be glad to update this post or reply you in the comments.

Upvotes: 1

Views: 105

Answers (1)

To Rakoto
To Rakoto

Reputation: 26

It is better for you to call the Add-in void directly from your Excel:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace YourSolution
{
    [ComVisible(true)]
    public interface IAddInUtilities
    {
        //You have to implement this
        void YourFunctionToCall();
    }

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public class AddInUtilities : IAddInUtilities
    {
        //Implemntation: Call this from your Excel
        public void YourFunctionToCall()
        {
             //The Operation here ...
        }
    }
}

Then from your Excel VBA, use this Sub:

Public Sub FunctionCallableInVBA()
    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("YourSolution")
    Set automationObject = addIn.Object

    'Call the Function your shared on the COM
    automationObject.YourFunctionToCall()

    Set addIn = Nothing
    Set automationObject = Nothing
End Sub

This should solve your issue and avoid you to search desperately for that in-existing hide/unhide event.

Upvotes: 1

Related Questions