none
Controlling application.enableevents in Excel from VSTO RRS feed

  • Question

  • How do I control application.enableevents in VSTO? I don't mean like "duh, just write into your code application.enableevents=thestateyouwant", but instead that I have an add-in that heavily (of course) relies in events and when it is running, I absolutely don't want the user to turn on VBA and forget the application.enableevents = false state on. Instead I want that the forgotten disabled events state to be automatically turned on when user returns to use the book I have specified (the add-in is relevant in only one workbook).

    So the code I want starts is something like

    Private Sub Application_WorkbookActivate(Wb as Microsoft.Office.Interop.Excel.Workbook) Handles Application.WorkbookActivate
    
     If Wb.Name = "This workbook where the add-in actually makes sense.xlsx"
      Wb.Application.EnableEvents = True
     End If
    End Sub

    But the key, of course, is that this has to work 100% reliably when the user has managed to use VBA to set the application.enableevents = false in somewhere else.

    What does my code miss?

    Monday, September 23, 2013 7:12 AM

Answers

  • Hi,

    According to your description, you want to set the EnableEvent property of the Application always to be true.

    Since someone could reset the property by VBA code, I suggest you to write an add-in with VSTO and use a Timer control to continually check the code “Wb.Application.EnableEvents = True” to achieve your goal.

    Here is a sample for your reference. In the code, you could set the interval property at which to raise the Elapsed event as you want.

    System.Timers.Timer time1 = new System.Timers.Timer();
    
    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        this.time1.Elapsed += new ElapsedEventHandler(OnTimedEvent);
        this.time1.Interval = 2000;
        this.time1.Start();
    }
    
    private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
    {
    }
    
    private static void OnTimedEvent(object source, ElapsedEventArgs e)
    {
        // stop the timer, do the task
        Timer time1 = source as Timer;
        time1.Stop();
        //System.Windows.Forms.MessageBox.Show("aaa");
        Excel.Workbook Wb = Globals.ThisAddIn.Application.ActiveWorkbook;
        if (Wb != null)
        {
            if (Wb.Name == "This workbook where the add-in actually makes sense.xlsx")
            {
               if (Wb.Application.EnableEvents == false)
               {
               	Wb.Application.EnableEvents = true;
               }
            }
        }
        // restart the timer to repeat after 2000 ms
        time1.Start();
    }


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by KilpAr Wednesday, September 25, 2013 11:50 AM
    Wednesday, September 25, 2013 11:28 AM
    Moderator

All replies

  • Hi,

    According to your description, you want to set the EnableEvent property of the Application always to be true.

    Since someone could reset the property by VBA code, I suggest you to write an add-in with VSTO and use a Timer control to continually check the code “Wb.Application.EnableEvents = True” to achieve your goal.

    Here is a sample for your reference. In the code, you could set the interval property at which to raise the Elapsed event as you want.

    System.Timers.Timer time1 = new System.Timers.Timer();
    
    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        this.time1.Elapsed += new ElapsedEventHandler(OnTimedEvent);
        this.time1.Interval = 2000;
        this.time1.Start();
    }
    
    private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
    {
    }
    
    private static void OnTimedEvent(object source, ElapsedEventArgs e)
    {
        // stop the timer, do the task
        Timer time1 = source as Timer;
        time1.Stop();
        //System.Windows.Forms.MessageBox.Show("aaa");
        Excel.Workbook Wb = Globals.ThisAddIn.Application.ActiveWorkbook;
        if (Wb != null)
        {
            if (Wb.Name == "This workbook where the add-in actually makes sense.xlsx")
            {
               if (Wb.Application.EnableEvents == false)
               {
               	Wb.Application.EnableEvents = true;
               }
            }
        }
        // restart the timer to repeat after 2000 ms
        time1.Start();
    }


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by KilpAr Wednesday, September 25, 2013 11:50 AM
    Wednesday, September 25, 2013 11:28 AM
    Moderator
  • Hi,

    According to your description, you want to set the EnableEvent property of the Application always to be true.

    Since someone could reset the property by VBA code, I suggest you to write an add-in with VSTO and use a Timer control to continually check the code “Wb.Application.EnableEvents = True” to achieve your goal.

    Here is a sample for your reference. In the code, you could set the interval property at which to raise the Elapsed event as you want.

    System.Timers.Timer time1 = new System.Timers.Timer();
    
    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        this.time1.Elapsed += new ElapsedEventHandler(OnTimedEvent);
        this.time1.Interval = 2000;
        this.time1.Start();
    }
    
    private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
    {
    }
    
    private static void OnTimedEvent(object source, ElapsedEventArgs e)
    {
        // stop the timer, do the task
        Timer time1 = source as Timer;
        time1.Stop();
        //System.Windows.Forms.MessageBox.Show("aaa");
        Excel.Workbook Wb = Globals.ThisAddIn.Application.ActiveWorkbook;
        if (Wb != null)
        {
            if (Wb.Name == "This workbook where the add-in actually makes sense.xlsx")
            {
               if (Wb.Application.EnableEvents == false)
               {
               	Wb.Application.EnableEvents = true;
               }
            }
        }
        // restart the timer to repeat after 2000 ms
        time1.Start();
    }


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    I think this will work especially with the check for the workbook. I will also use some boolean that indicates whether I really wanted myself to turn the events off and that way I can still switch the state the way I want. I need to add a boolean to the workbook.deactivate event also to say that "now it's not under my control anymore, timer, keep track of this".

    I'd say your answer is, if not perfect, then at least the bump into the right direction I needed. Thanks!

    Wednesday, September 25, 2013 11:50 AM