Suppress MsgBox from another Subroutine in VBA

Question

I have a VBA sub that makes a call to a sub that was written by someone else. occasionally, the other sub opens a MsgBox with an OK button. The other sub takes a long time to run, and I am calling it hundreds of times, so I want to be able to run this overnight. Unfortunately, I can't figure out a way to automatically click OK on the MsgBox.

I have tried

Application.DisplayAlerts = False

but this doesn't suppress message boxes.

Is there any way to do this?

Thanks

Answer 1

One way to do this is slightly modifying the code of the original sub. You will need to have the necessary permissions tough...

• Modify the header of the original sub by throwing in an extra optinal parameter at the end setting the default value to True. This will result in something like Sub OriginalSubName(original set of parameters, Optional ShowMessages = True)

• At the point where the msgbox is called, modify the code this way:

  If showMessages = True Then 'The = True part is important here - see below.
  showMessages is a Variant type
      'The original statement that calls the msgBox
  End If
  

• Leave the rest of the code of the original sub unchanged

• Modify the line where you call the original sub by throwing in False as an extra parameter. This results in OriginalSubNameyour set of parameters, False. This way you don't suppress the dialog box by default, but you do when you use it in your sub.

Wonder why I use an optional Variant type parameter?

• The optional part: this prevents other existing subs from crashing when they call the modified sub
• The Variant type part: optional parameters are always Variant type. That's also why you need to use If showMessages = True Then instead of just If showMessages Then.