Custom Ribbon - Disable/Enable Buttons for long running macro

Apr 15, 2015 at 8:55 AM
Hello Govert,

I have a long running macro for which I wish to disable the button that starts it running and several other buttons that are associated with the process. Then when the macro finishes
the disabled buttons should br enabled.
 public void DoRemoveCellMonitor(IRibbonControl control)
        {
            if (RemoveWsMonEnabled)
            {
                RemoveWsMonEnabled = false;
                InsertWsMonEnabled = false;
                ribbon.InvalidateControl("btnInsertWsMon");
                commands.DoRemoveCellMonitor(control);
                RemoveWsMonEnabled = true;
                InsertWsMonEnabled = true;
                ribbon.InvalidateControl("btnInsertWsMon");
            }
        }
I won't say the above code doesn't work, because it does sort of, the problem seems to be that invalidating a control seems to block until after the method exits. This makes sense because the code must run on a single thread.

I can get the disabling and enabling to work if I enclose the work to be performed in a backgroundworker thread but this has other unpredictable consequences.

Do you know of any way of getting this to work?

Regards

pianoboy
Coordinator
Apr 15, 2015 at 9:16 AM
Simplest is just to do your work on the main thread. Then you might not need to update the UI - the user won't be able to interact with Excel anyway.

In general I recommend not talking to the Excel object model from another thread. It causes many problems, and does not help performance.
But you might like to do long-running work, like talking to a database or web service, and then some calculations on another thread. In the end to put the results back into Excel you should transition back the the main thread, before using the COM object model. Excel-DNA has a helper to run the code back on the main thread - you call ExcelAsyncUtilt.QueueAsMacro(...) with the work to do.

Then for your button - the work gets kicked off in the callback (where you then disable the buttons etc.). The callback then returns and the UI should update, while the background work continues. Once done, the background worker calls QueueAsMacro(..) and on the main thread it might talk to Excel again (say to write data to the sheet), and then enable the ribbon buttons.

-Govert
Apr 15, 2015 at 3:54 PM
Hello Govert,

Thanks for the quick response. I had forgotten about QueueAsMacro.
I have taken your advice and now have something that works. For the benefit of anyone else struggling to implement something similar, here is the relevant code I have used.
 private void InvalidateGrpWorksheet(bool enable)
       {
           
               grpWorksheetEnabled = enable;
               ribbon.Invalidate();
               
       }

 public bool GetGrpWorksheetEnabled(IRibbonControl control)
        {
            
            return grpWorksheetEnabled;

        }


 public void DoRemoveCellMonitor(IRibbonControl control)
        {
              
                BackgroundWorker worker = new BackgroundWorker();
                worker.DoWork += (obj, args) =>
                {
                    System.Threading.Thread.Sleep(250);
                    ExcelAsyncUtil.QueueAsMacro(() => {
                        commands.DoRemoveCellMonitor(control);
                    });
                };
                worker.RunWorkerCompleted += (obj, args) =>
                {
                    ExcelAsyncUtil.QueueAsMacro(() => {
                        RemoveWaitPointer();
                        InvalidateGrpWorksheet(true);
                    });
                };

                SetWaitPointer();
                InvalidateGrpWorksheet(false);
                worker.RunWorkerAsync();
                
                
                
        }
I wanted to disable 5 buttons. Notice that I have a sleep thread in the backgroundworker to delay the DoRemoveCellMonitor command which monopolized the gui thread and would not allow all getenabled callbacks to complete. If you only wish to disable the button with focus then you may not need the sleep thread.

Once again thanks for your help and the fantastically well thought out Excel-DNA.

Regards

Pianoboy