[SOLVED] How to temporarily disable the Worksheet.Activate Event?

How to temporarily disable the Worksheet.Activate Event?

I'm working on an Excel workbook that uses SAP's BusinessObjects Analysis plug-in. Whenever the workbook opens, a variable selection prompt is triggered and allows users to enter values which are then used to select data from the back-end and display it in a table format in Excel(a representation of the SAP BW Query in Excel). The problem I'm facing is the following... Whenever the prompt selection is complete and the selection is being processed, if you spam click the worksheet tabs you fire the Worksheet.Activate event. The problem is that Worksheet.Activate handler tries to invalidate a ribbon object which itself has not been set yet. I want to prevent that and I would like the ribbon to be invalidated only if it has been set. The problem is that the ribbon must be invalidated when the worksheet is changed in order for it to load the specific buttons in the ribbon that are unique for every worksheet. Not invalidating on activating the worksheet is not an option. It would be best to eliminate the possibility of changing worksheets, before our custom ribbon has loaded.

Here's what I have tried without any success:

I tried disabling interactive mode with Application.Interactive = False as soon as the prompt appears and re-enabling it whenever the AOCust_OnLoad(ribbon As IRibbonUI) ribbon onLoad callback has been executed and the ribbon is set.
I tried disabling all events using Application.EnableEvents = False as soon as the prompt appears and re-enabling them whenever the ribbon onLoad callback has been executed and the ribbon is set.
I tried adding the following to the Worksheet.Activate callback but it never exits the infinite loop which is probably expected because ribbon onLoad is not a system event and is thus never triggered.
```
while ribbon is Nothing
  'waiting for ribbon onload to fire and set the value
  DoEvents
wend
```

What is surprising with the second case is how the Worksheet.Activate event is fired when the events, to my understanding, are disabled.

Do you have any idea of how I can approach this problem so that the user cannot change the worksheet before the ribbon has been set?

Please let me know if you would like me to add code snippets to this description.

Thanks

EDIT - CODE ADDED

In Microsoft Excel Objects in ThisWorkbook

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If EnableEvents = True Then
      Debug.Print "invalidate"
      Call AOCust_Callbacks.AOCust_InvalidateRibbon
  End If
End Sub

In Module "Custom_Ribbon"

'Callback for Selections data onAction
 Sub PROMPT(control As IRibbonControl)
   EnableEvents = False
   'more prompt-related code
 End Sub

In Module 'AOCust_Callbacks"

Public EnableEvents As Boolean

'Callback for Ribbon OnLoad
Sub AOCust_OnLoad(ribbon As IRibbonUI)
  Debug.Print "setting the ribbon"
  'Start Highlighting for Workbook
  StartHighlighting
  Set AOCustRibbon = ribbon
  Debug.Print "the ribbon is now set"
  EnableEvents = True
End Sub

When I spam click the worksheet tabs as soon as the prompt selection is complete and the selection is still processing, I no longer get the problem with the variable not being set when I try to invalidate the ribbon because of the if statement, but the problem is that the onload ribbon function is not executed.

Solution

The problem was resolved by disabling the mouse events using Application.interactive = False before the selection prompt loads or the first time and re-enabling them when the ribbon has loaded. The problem was that SAP decided to use a different callback for the very first prompt called onBeforeFirstPromptsDisplay.As a result, my changes to the prompt callback that's in the workbook were never actually triggered because that callback is only executed when you trigger the prompt yourself through the workbook. After implementing onBeforeFirstPromptsDisplay everything worked. Thanks for your input!