CODEWITHSUNDEEP
excelvba
LucasSeveryn
LucasSeveryn

Reputation: 6262

Is it possible for xlam Excel addin to override a sub from a workbook?

The xlam Excel addin I'm building is interacting with externally sourced spreadsheet. The trouble is that the sheet, among other things in Workbook Change event has code that throws a MsgBox popup, messing with automation as it waits for user input. The popup is not called directly - the Workbook Change event is calling a custom Sub called "ShowPopup". Then inside the "ShowPopup" we have MsgBox call.

My thought was that if I could override the ShowPopup sub, I could prevent it from creating the MsgBox. Is it possible to do?

Constraints to the potential solutions:

Upvotes: 0

Views: 106

Answers (1)

LucasSeveryn
LucasSeveryn

Reputation: 6262

I managed to solve my problem, although not in the way I originally planned. In the end it involves overriding the MsgBox Function.

For future reference here are the steps.

  1. In any of .xlam addin modules, create a global variable Global macroRunning As Boolean
  2. In the sub where automation loop sits, we put this before the loop: macroRunning = True and opposite after the loop.

  3. Create a new module, I called it MsgBoxHack and put this code inside:

    Public Function MsgBox( _
Prompt, _
Optional Buttons As VbMsgBoxStyle = vbOKOnly, _
Optional Title, _
Optional HelpFile, _
Optional Context _
) _
As VbMsgBoxResult

  If IsMissing(Title) Then
      Title = "Overriden MsgBox"
  End If

  If macroRunning = False Then
      MsgBox = VBA.Interaction.MsgBox(Prompt, Buttons, Title, HelpFile, Context)
  End If

End Function
    1. That's it, when your global variable macroRunning is set to True you won't see any MsgBoxes

Downside - This overriden MsgBox function will run on all workbooks you work on while the addin is enabled in Excel.

Upvotes: 1

Related Questions