Disabling the Ribbon when editing in Formula Bar

[Andrew Lockhart]

Hi,

We’ve seen an annoying feature of custom Ribbons that I wonder if it’s possible to solve with ExcelDNA.

When the user is editing in the Formula Bar the built in Ribbons disable all the controls on them, while our Custom Ribbon does not. Other MS Addins that have Ribbons like Inquire and OffCat also don’t disable themselves but their functionality still works so perhaps this is by design.

Our macros will not work if the user is editing the formula so ideally I would like to disable the Ribbon buttons that launch them. Any idea if this is possible?

If the above isn’t possible then I guess I have two options, either somehow detect what’s going on a tell the user, or somehow stop the formula editing (maybe select another cell) and then the macros will run - none of these options are great.

Any suggestions appreciated.

Regards

[Govert van Drimmelen]

Hi Andrew,

You could check whether the user is editing inside your ribbon macros by using the PenHelper API:

https://github.com/Excel-DNA/IntelliSense/blob/master/Source/ExcelDna.IntelliSense/UIMonitor/XlCall.cs

and then code like this:

// The call to LPenHelper will cause an AccessViolation after Excel starts shutting down.
// .NET40: If this library is recompiled to target .NET 4+, we need to add an attribute to indicate that this exception
// (which might indicate corrupted state) should be handled in our code.

[HandleProcessCorruptedStateExceptions]
static int CallPenHelper(int wCode, ref XlCall.FmlaInfo fmlaInfo)
{
try
{
// (If Excel is shutting down, we see an Access Violation here, reading at 0x00000018.)
return XlCall.LPenHelper(XlCall.xlGetFmlaInfo, ref fmlaInfo);
}
catch (AccessViolationException ave)
{
throw new InvalidOperationException("LPenHelper call failed. Excel is shutting down.", ave);
}
}

static bool IsInFormulaEditMode()
{
// check edit state directly
var fmlaInfo = new XlCall.FmlaInfo();

// If Excel is shutting down, CallPenHelper will throw an InvalidOperationException.
var result = CallPenHelper(XlCall.xlGetFmlaInfo, ref fmlaInfo);
if (result == 0)
{
// Succeeded
return fmlaInfo.wPointMode != XlCall.xlModeReady;
}
return false; // We don't really know...
}

-Govert

________________________________________
From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Andrew Lockhart [andrewl...@gmail.com]
Sent: 31 August 2017 07:59 PM
To: Excel-DNA
Subject: [ExcelDna] Disabling the Ribbon when editing in Formula Bar

Hi,

Any suggestions appreciated.

Regards

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Andrew Lockhart]

That's very helpful. If only there were an event that we could listen to to enable/disable the Ribbon. But at least your solution will mean we can prevent the error.

Thanks.

[Sergey Nestertsev]

I did this with the following approach:

1. add System.Windows.Forms.Timer, which runs on UI thread and checks every 500ms that one of built-in commands, becomes grayed.

2. keep current edit mode in internal variable and in case if it is changes (from Ready to Edit and vice versa) - call corresponding action. In my case, the action is invalidate entire ribbon.

3. on every button in ribbon (custom ui), that should be disabled at Edit Mode - set "getEnabled" attribute.

Here some code, which can help you to get the idea (BTW, for operations with COM-interface of Excel I use NetOffice):

        System.Windows.Forms.Timer _editModeUpdateTimer = null;

        private bool _isUpdatingEditMode = false;

        private bool? _editMode = null;

        public void OnRibbonLoad(IRibbonUI sender)

        {

            _ribbonUI = sender;

            Application app = new Application(null, ExcelDnaUtil.Application);

            app.WindowActivateEvent += App_WindowActivateEvent;

            app.WindowDeactivateEvent += App_WindowDeactivateEvent;

            _editModeUpdateTimer = new System.Windows.Forms.Timer

            {

                Interval = 500

            };

            _editModeUpdateTimer.Tick += _editModeUpdateTimer_Tick;

        }

// NOTE: start timer when window is active

        private void App_WindowActivateEvent(Workbook Wb, Window Wn)

        {

            if (_editModeUpdateTimer != null)

            {

                _editModeUpdateTimer.Start();

            }

        }

        private void App_WindowDeactivateEvent(Workbook Wb, Window Wn)

        {

            if (_editModeUpdateTimer != null)

            {

                _editModeUpdateTimer.Stop();

            }

        }

        /// <summary>

        /// Timer for monitoring changes of Edit Mode in Excel.

        /// </summary>

        /// <remarks>Works in UI thread</remarks>

        private void _editModeUpdateTimer_Tick(object sender, EventArgs e)

        {

            if (UpdateEditMode())

            {

                InvalidateRibbon();

            }

        }

        /// <summary>

        /// Updates status of Excel Edit mode.

        /// </summary>

        /// <returns>

        /// TRUE - edit mode has changed since last call. 

        /// FALSE - edit mode unchanged.

        /// </returns>

        private bool UpdateEditMode()

        {

            bool result = false;

            if (_isUpdatingEditMode) return result;

            try

            {

                _isUpdatingEditMode = true;

                Application app = null;

                bool? newEditMode = null;

                try

                {

                    if (ExcelDnaUtil.Application == null) return result;

                    app = new Application(null, ExcelDnaUtil.Application);

                    if (app.Interactive == false)

                    {

                        newEditMode = false;

                    }

                    else

                    {

                        try

                        {

                            NetOffice.OfficeApi.CommandBars commandBars = app.CommandBars;

                            NetOffice.OfficeApi.CommandBar commandBar = commandBars["Worksheet Menu Bar"];

                            NetOffice.OfficeApi.CommandBarControl oNewMenu = commandBar.FindControl(NetOffice.OfficeApi.Enums.MsoControlType.msoControlButton, 18, null, null, true);

                            if (oNewMenu != null)

                            {

                                newEditMode = !oNewMenu.Enabled;

                            }

                        }

                        catch

                        {

                            newEditMode = true;

                        }

                    }

                }

                catch (COMException c)

                {

                    if ((uint)c.ErrorCode == ExcelCOMErrorCodes.RPC_E_SERVERCALL_RETRYLATER)

                        newEditMode = true;

                }

                if (newEditMode.HasValue && (!_editMode.HasValue || _editMode.Value != newEditMode.Value))

                {

                    _editMode = newEditMode;

                    result = true;

                }

            }

            finally

            {

                _isUpdatingEditMode = false;

            }

            return result;

        }

        /// <summary>

        /// Current Edit Mode of Excel

        /// </summary>

        private bool IsInEditMode

        {

            get

            {

                return _editMode ?? false;

            }

        }