title: VBA - How to temporarily disable the Worksheet.Activate Event?
body: <p>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 <code>Worksheet.Activate</code> event. The problem is that <code>Worksheet.Activate</code> 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. </p>
  
  <p>Here's what I have tried without any success:</p>
  
  <ol>
  <li><p>I tried disabling interactive mode with <code>Application.Interactive = False</code> as soon as the prompt appears and re-enabling it whenever the  <code>AOCust_OnLoad(ribbon As IRibbonUI)</code> ribbon <code>onLoad</code> callback has been executed and the ribbon is set.</p></li>
  <li><p>I tried disabling all events using <code>Application.EnableEvents = False</code> as soon as the prompt appears and re-enabling them whenever the ribbon <code>onLoad</code> callback has been executed and the ribbon is set.</p></li>
  <li><p>I tried adding the following to the <code>Worksheet.Activate</code> 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.</p>
  
  <pre><code>while ribbon is Nothing
    'waiting for ribbon onload to fire and set the value
    DoEvents
  wend
  </code></pre></li>
  </ol>
  
  <p>What is surprising with the second case is how the <code>Worksheet.Activate</code> event is fired when the events, to my understanding, are disabled.</p>
  
  <p>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?</p>
  
  <p>Please let me know if you would like me to add code snippets to this description.</p>
  
  <p>Thanks</p>
  
  <p>EDIT - CODE ADDED</p>
  
  <p>In Microsoft Excel Objects in ThisWorkbook</p>
  
  <pre><code>Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If EnableEvents = True Then
        Debug.Print "invalidate"
        Call AOCust_Callbacks.AOCust_InvalidateRibbon
    End If
  End Sub
  </code></pre>
  
  <p>In Module "Custom_Ribbon"</p>
  
  <pre><code>'Callback for Selections data onAction
   Sub PROMPT(control As IRibbonControl)
     EnableEvents = False
     'more prompt-related code
   End Sub
  </code></pre>
  
  <p>In Module 'AOCust_Callbacks"</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.  </p>