Disable my addin button when workbook is closed

Add-in Express™ Support Service
That's what is more important than anything else

Disable my addin button when workbook is closed
 
Ranjitha Nagendra




Posts: 40
 Joined: 2015-10-26
Hello addin express team,

I want to disable my custom com addin buttons when there are no open workbooks, but excel application is still running. I tried placing the code for "DisableButtons" in adxExcelAppEvents1_WorkbookDeactivate. Buttons will be disabled if excelApp is null or excelApp.ActiveWorkbook is null or excelApp.ActiveWorkBook.ActiveSheets = 0. But when this event fires, workbook is still open and excelApp is not null. How do I achieve this?

Thanks in advance,
Ranjitha
Ranjitha
Posted 16 Jan, 2017 05:50:29 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Ranjitha,

Ranjitha Nagendra writes:
Buttons will be disabled if excelApp is null or excelApp.ActiveWorkbook is null or excelApp.ActiveWorkBook.ActiveSheets = 0.


1. excelApp can't be null.

2. Test whether excelApp.ActiveWorkbook returns the workbook being deactivated or some other workbook when called from the WorkbookDeactivate event.

3. You need to check if there are *visible* windows in Excel (excluding the windows of the workbook being closed).


Andrei Smolin
Add-in Express Team Leader
Posted 16 Jan, 2017 08:12:15 Top
Ranjitha Nagendra




Posts: 40
 Joined: 2015-10-26
Hi Andrei,

There is always 1 visible window even if all workbooks are closed. "Book1" is always a visible window.

if(GetVisibleWindowsCount() == 0)
DisableButtons();

But DisableButtons is never getting called.

Basically my requirement is to disable the user from clicking on any addin button, when there are no workbooks and when there are no worksheets in the workbook.

I am trying to do this in adxExcelAppEvents1_WorkbookDeactivate event. Where am I going wrong? Any help would be greatly appreciated.

Regards,
Ranjitha
Ranjitha
Posted 17 Jan, 2017 07:43:30 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Ranjitha,

Here's what I wrote:

Andrei Smolin writes:
You need to check if there are *visible* windows in Excel (excluding the windows of the workbook being closed).


If there's no window visible, you can disable the buttons.


Andrei Smolin
Add-in Express Team Leader
Posted 17 Jan, 2017 07:56:50 Top
nwein




Posts: 577
 Joined: 2011-03-28
I'm also disabling my ribbon buttons when there's no workbook open; The way I achieve this is by hooking up to the WorkbookBeforeClose event (in the ADXExcelAppEvents) and inside that event handler I have code similar to this (this is just a snippet to get you going): 
Workbooks workbooks = ExcelApp.Workbooks;
if (workbooks.Count == 1)
{ 
     // disable my buttons
}
Posted 17 Jan, 2017 10:48:58 Top
Ranjitha Nagendra




Posts: 40
 Joined: 2015-10-26
Hi nwein,

That works perfectly, thanks

Regards,
Ranjitha
Ranjitha
Posted 18 Jan, 2017 04:18:41 Top
nwein




Posts: 577
 Joined: 2011-03-28
No problem, the only thing you might need to consider is the case when there are any unsaved changes on the workbook and the user closes it. In that scenario the user can cancel the close book event yet the buttons get disable (since the prompt to save/don't save/cancel appears after the event).
You can use this code to overcome this if you'd like: 
void WorkbookBeforeClose(object sender, ADXHostBeforeActionEventArgs e)
{
	Workbooks                        workbooks = null;
	Workbook                         workbook  = null;
	Microsoft.Office.Core.FileDialog dialog    = null;
	try
	{		
		workbooks 			= ExcelApp.Workbooks;
		workbook 			= ExcelApp.ActiveWorkbook;
		string workbookName = workbook.Name;

		// Handle scenarios where workbook prompts to close 
		// and a user cancels the close workbook event
		if (!workbook.Saved)
		{
			DialogResult result = MessageBox.Show($"Do you want to save the changes you made to '{workbookName}'?", "Microsoft Excel", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning);
			switch (result)
			{
				case DialogResult.Yes:
					if (string.IsNullOrEmpty(workbook.Path)) // check if workbook has never been saved before
					{
						dialog = ExcelApp.FileDialog[MsoFileDialogType.msoFileDialogSaveAs];
						dialog.InitialFileName = workbookName;
						if (dialog.Show() == 0) // -1 action button, 0 cancel button
						{
							e.Cancel = true;
							return;
						}
						dialog.Execute();
					}
					else
						workbook.Save();
					break;
				case DialogResult.No:
					workbook.Saved = true;
					break;
				case DialogResult.Cancel:
					e.Cancel = true;
					return;
			}			 
		}

		if (workbooks.Count == 1)
		{
			// Disable your buttons
		}  		
	}
	catch
	{
		// handle
	}
	finally
	{
		if (workbook  != null) Marshal.ReleaseComObject(workbook);
		if (workbooks != null) Marshal.ReleaseComObject(workbooks);
		if (dialog    != null) Marshal.ReleaseComObject(dialog);
	}
}
Posted 18 Jan, 2017 10:38:31 Top
Ranjitha Nagendra




Posts: 40
 Joined: 2015-10-26
Thanks a lot nwein. That works exactly how I want.
Ranjitha
Posted 20 Jan, 2017 01:39:04 Top