Code to disable all message boxes without deleting them

novice_user · Jun 15th 2020

Hi All,

I am writing a piece of code to open & run macros looping through the files, but every code run individually has a message box at the end for "code was ran without incident". I was wondering whether there is a way to disable this message box so the code can run through multiple files without having to click the space bar? I do not want to remove this message box, because when the individual runs the code in one file it is useful to have the message box there. Also, I have tried to copy the code & rewrite without the message box but this is someone else's code & it is very complex meaning it jumps a lot between different modules for different purposes so it would take a while to go through every sub & repeat but without a message (error prone too). So, I was wondering is there another alternative which I can add into the below code to automatically disable/close message boxes? (screen updating/displays do not work)

Code

Private Sub LoopThroughFiles()

Application.EnableAnimations = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Dim ws As Worksheet
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xlsm*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
            Set ws = ActiveWorkbook.Sheets("Overview")
            
                ws.Cells(1, 4) = Sheet1.Range("a1")
                Application.Run "'" & xFileName & "'!SAP"
                Application.Run "'" & xFileName & "'!PlayAll"
                .Save
                .Close
                  
            End With
            xFileName = Dir
        Loop
    End If
    
Application.EnableAnimations = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

MsgBox "All files successfully updated"

End Sub

Display More

**royUK** · Jun 15th 2020

The only way that I can think of is to add a boolean variable in each workbook that confirms the msgbox runs. This would be set to False from the code above.

novice_user · Jun 15th 2020

Quote from royUK

The only way that I can think of is to add a boolean variable in each workbook that confirms the msgbox runs. This would be set to False from the code above.

Could you please explain further how to do this ?

**royUK** · Jun 15th 2020

Attach an an example file that runs the main code and one of the files it opens. It doesn't need any confidential data.

novice_user · Jun 15th 2020

Quote from royUK

Attach an an example file that runs the main code and one of the files it opens. It doesn't need any confidential data

One of the files it opens contains a lot of code which I don't believe is confidential but the file is pretty complex so it may have more info than I know. Is it possible to delete a thread once resolved?

**rory** · Jun 15th 2020

I'd suggest you just amend the declaration of the macros that are run so they look like:

Code

Sub MacroName(Optional ShowMessages As Boolean = True)

then amend the relevant msgbox lines so that they start with:

Code

If ShowMessages Then

Then when you want to disable the messages from the calling code, simply pass the optional argument as False.

**royUK** · Jun 15th 2020

We do not delete threads when resolved. This is a help forum that is free. If you want confidentiality then use our pay for help Forum - Hire Help

novice_user · Jun 15th 2020

Quote from rory
I'd suggest you just amend the declaration of the macros that are run so they look like:
Code
Sub MacroName(Optional ShowMessages As Boolean = True)
then amend the relevant msgbox lines so that they start with:
Code
If ShowMessages Then
Then when you want to disable the messages from the calling code, simply pass the optional argument as False.
Display More

Ahh great thank you. For example:

Code

     Sub SCallGetSAPData(Optional ShowMessages As Boolean = True)
        Dim lVar_Calculation As Variant
            lVar_Calculation = FInitializeFunction_Var
            gStr_CallingFunction = "SCallGetSAPData"
            gStr_CallingFunctionPart = "Main Code"
            
                If Not FSetGlobalVariables_Bln Then GoTo Finally
                If Not FGetSAPDataKE5Z_Bln(False, All) Then GoTo Finally
                If Not FGetSAPDataZREP18_Bln(False) Then GoTo Finally
                 
                If ShowMessages Then MsgBox "SAP data were downloaded without incident."
Finally:
            STerminateFunction (lVar_Calculation)
    End Sub

Display More

And in my file where I am trying to run all, I would set show messages = false?

Code

Private Sub LoopThroughFiles()

Application.EnableAnimations = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Dim ShowMessages as Boolean
    Dim ws As Worksheet
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xlsm*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
            Set ws = ActiveWorkbook.Sheets("Overview")
            Set showMessages = False
                ws.Cells(1, 4) = Sheet1.Range("a1")
                Application.Run "'" & xFileName & "'!SAP"
                Application.Run "'" & xFileName & "'!PlayAllWithoutMsg"
                .Save
                .Close
                  
            End With
            xFileName = Dir
        Loop
    End If
    
Application.EnableAnimations = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

MsgBox "All files successfully updated"

End Sub

Display More

**rory** · Jun 15th 2020

No, you pass False as an argument:

Code

Application.Run "'" & xFileName & "'!SAP", False

for example.

novice_user · Jun 15th 2020

Quote from rory
No, you pass False as an argument:
Code
Application.Run "'" & xFileName & "'!SAP", False
for example.

Ahh that is amazing! Thank you!! I will edit & let you know how it goes! thanks so much!

dangelor · Jun 15th 2020

Could you not just run a find/replace to turn those lines into comments...

...and then reverse it when you're done?

novice_user · Jun 16th 2020

Quote from rory
No, you pass False as an argument:
Code
Application.Run "'" & xFileName & "'!SAP", False
for example.

Hi Rory,

After searching & messing around I found in the code the following:

Code

If pBln_EnableFinalMsg Then
MsgBox "KE5Z data were downloaded without incident."
End If

And this is defined as:

Code

Function FGetSAPDataZREP18_Bln(ByVal pBln_EnableFinalMsg As Boolean _
                                    ) As Boolean

And this relates to the code I am calling from my first book. I will be calling SCallGetSAPDataZREP18 (). Please see below:

Code

 Sub SCallGetSAPDataZREP18()
        Dim lVar_Calculation As Variant
            lVar_Calculation = FInitializeFunction_Var
            gStr_CallingFunction = "SCallGetSAPDataZREP18"
            gStr_CallingFunctionPart = "Main Code"
            
                If Not FSetGlobalVariables_Bln Then GoTo Finally
                Call FGetSAPDataZREP18_Bln(True)
Finally:
            STerminateFunction (lVar_Calculation)
    End Sub

Display More

Is there a way to set the the

Code

pBln_EnableFinalMsg

As false/not activate the message from my code in the book I want to run them all? I have tried searching to understand byVal & whether theres a way to deactivate this in my file but I am not sure.

Thanks,

Mel

novice_user · Jun 16th 2020

Quote from dangelor

Could you not just run a find/replace to turn those lines into comments...

...and then reverse it when you're done?

Thank you for your input! As this file will be run monthly (for approx 50 files), the find & replace method would require too much work & would be easier to remove the messages completely! Thanks for the input though!:)

**rory** · Jun 16th 2020

It looks like you could just run the function directly using:

Code

Application.Run "'" & xFilename & "'!FGetSAPDataZREP18_Bln", True

Code to disable all message boxes without deleting them

Participate now!

Share

Tags