Excel VSTO how to check if the Cancel button has been triggered from the dialog after the WorkbookBeforeClose event is fired?

Question

I am currently developping an Excel VSTO addin and here is the code for WorkbookBeforeClose

private void App_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
    {
        bool isEnabled = false;
        setRibbonControlState(ref isEnabled);
    }

In this code I disable the ribbon if there is no workbook left opened. But if I press the Cancel button from the dialog after I tried to close Excel, the ribbon gets disabled anyway. But as the WorkbookBeforeClose event passed a Cancel parameter, I don't know how to set that parameter when I press the button, how do I check the dialog prompted for the button that has been triggered.

All cases I have seen so far implement a dialog in the body of the WorkbookBeforeClose handler, but I don't want to implement a custom dialog, I would like to use the one provided by default.

Thanks!

Vitaliy Prushak Vitaliy Prushak · Accepted Answer · 2020-01-27T10:44:10

As of my VBA experience this

but I don't want to implement a custom dialog, I would like to use the one provided by default.

is impossible, because that dialog appears just after the before_close event.
The only (known by me) way to manage this stuff - is to create own SaveChanges dialog, which is, by the way, very simple and not every user will notice the difference. And moreover, it will do the same job as default prompt.
The other thing you should pay attention to - is that there may be at least one invisible workbook. Even if you see such screen:
there is a chance that this.Application.Workbooks.Count will show you 1, instead of 0. This is due to the possibility that user has its own Personal.xlsb workbook, which is invisible, but nevertheless, is loaded with an Excel.Application. So in case you want to disable your ribbon properly - you should consider this as well.

Here is my example of this solution:

public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            this.Application.WorkbookBeforeClose += ApplicationOnWorkbookBeforeClose;
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        // Catch the before close event
        private void ApplicationOnWorkbookBeforeClose(Excel.Workbook wb, ref bool cancel)
        {

            if (!wb.Saved)
            {
                switch (MessageBox.Show(text:$"Do you want to save changes you made to '{this.Application.ActiveWorkbook.Name}'?", 
                    caption:"Microsoft Excel",buttons:MessageBoxButtons.YesNoCancel, icon:MessageBoxIcon.Exclamation))
                {
                    case DialogResult.Cancel: // case want to cancel - break the closing event
                        {
                            cancel = true;
                            return;
                        }
                    case DialogResult.Yes: // case user want to save wb - save wb
                        {
                            wb.Save();
                            break;
                        }
                    case DialogResult.No: // case user don't want to save wb - mark wb as saved to avoid the application messagebox to appear
                        {
                            wb.Saved = true;
                            break;
                        }
                }
            }

            if (IsAnyWorkbookOpen())
            {
                // replace this with your code
                MessageBox.Show("Some books will still be open, don't turn off the ribbon");
                return;
            }
            // replace this with your code
            MessageBox.Show("All books will be closed");
        }

        private bool IsAnyWorkbookOpen()
        {
            // check that remaining amount of open workbooks without the one being closed is greater that 2
            if (this.Application.Workbooks.Count - 1 > 2)
            {
                return true;
            }
            // IF the count of workbooks is 2 one of them maybe a PERSONAL.xlsb
            else if (this.Application.Workbooks.Count == 2)
            {
                foreach (Excel.Workbook wb in this.Application.Workbooks)
                {
                    if (!wb.Name.Equals(this.Application.ActiveWorkbook.Name))
                    {
                        // In case when one of two open workbooks is Personal macro book you may assume that 
                        // there will be no open workbooks for user to work directly
                        if (wb.Name.Equals("Personal.xlsb".ToUpper()))
                        {
                            return false;
                        }
                    }
                }
                // In case when NONE of two open workbooks is a Personal macro book
                // there will be at least one open workbook for user to work directly
                return true;
            }
            else
            {
                return true;
            }
        }


        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }

        #endregion
    }

And last thing - if you disable your ribbon, but application will still be running - you will have to enable it again on workbook_activate event.

Note I'm just moving from VBA to VSTO - so any comments are highly appreciated.

Excel VSTO how to check if the Cancel button has been triggered from the dialog after the WorkbookBeforeClose event is fired?

1 Answers