VBA warningsign pop up but no error message

[torbenheldam]

Hello all.

I am trying to do a macro which should be very simple. It will open all files in a specified folder one by one, set calculation to automatic to extract data from our reporting system (IBM Cognos/TM1 running on our Citrix server), save the file, close it and continue with the next file.

Sometimes the macro bugs. There is no error description, just an empty box with a warningsign. I can click and it will continue. Sometimes I will click once, other times it will keep popping up.
I have used do events, inserted waits etc. as I feared it could be because the macro was too fast. The files have a lot of tabs and takes a while to calculate.

When I run the macro stepwise,it works like a charm.

I hope that one of you geniuses can help me solve this.

Thanks for your time anyway.

 

[JLGWhiz]

It would help if you posted the macro. and a screen shot of the warning if possible.

 

[torbenheldam]

Hello JLGWhiz,

Thanks for your reply. I have attached the screenshot and pasted the entire macro below:

Sub RefreshAllFilesInFolder()

Dim wb As Workbook
Dim filePath, month, year, fileName, fileType As String
Dim a, b As Date
Dim folder As Object
Dim i As Integer
Dim n As Integer
Dim LastRow As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Clear the list of processed files
LastRow = ThisWorkbook.Sheets(1).Cells(Rows.Count, 6).End(xlUp).Row
ThisWorkbook.Sheets(1).Range("F6:F" & LastRow + 1).Clear

'Retrieve Target Folder Path from the sheet
filePath = ThisWorkbook.Sheets(1).Cells(9, 2) & "\"

fileType = "*.xls*"

'Obtain the filename of the excel file in the folder
fileName = Dir(filePath & fileType)

'Obtain the parameters
month = ThisWorkbook.Sheets(1).Cells(7, 2)
year = "Year " & ThisWorkbook.Sheets(1).Cells(6, 2)

i = 4

a = Now()

'Loop through each Excel file in folder
  Do While fileName <> ""
    'Disable popups
    Application.DisplayAlerts = False

    'Check if file is open already. It will be read-only
        If IsFileOpen(filePath & fileName) = True Then
              MsgBox fileName & " is already open. Please close the file and re-run the macro!"
              Exit Sub
        End If

    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(fileName:=filePath & fileName)

    'Ensure Workbook has opened before moving on to next line of code.
    DoEvents

    'Write the filename in the statusbar
    Application.StatusBar = "Updating file " & fileName

   'When workbook is opened paste the parameters in the file
    ActiveWorkbook.Sheets("****pit").Cells(11, 3) = month
    ActiveWorkbook.Sheets("****pit").Cells(15, 3) = month
    ActiveWorkbook.Sheets("****pit").Cells(2, 4) = year

    'Set calculation to automatic and force Excel to finish calculations before continuing
    Application.Calculation = xlCalculationAutomatic

        'If calculation goes wrong, exit the sub and reset application settings. Loop until calculation is finished
        On Error GoTo ErrorHandler
            n = 0
            Do Until Application.CalculationState = xlDone
           DoEvents
            n = n + 1
        Loop

    'If the user is not connected to TM1 the field Project will be empty after calculation. Then exit the macro
    If ActiveWorkbook.Sheets("****pit").Cells(4, 3) = "" Then
        MsgBox "You are not connected to TM1. Please connect and restart the macro!"
            Application.DisplayAlerts = True
            Application.Calculation = xlCalculationManual
            Application.ScreenUpdating = True
            Application.StatusBar = "Ready"
            Application.StatusBar = False
        Exit Sub
    End If

      'Add the filename to the list of completed files
      ThisWorkbook.Sheets(1).Cells(i + 2, 6) = fileName
      ThisWorkbook.Sheets(1).Activate
      ThisWorkbook.Sheets(1).Cells(i + 2, 6).Select
                With Selection.Font.ThemeColor = xlThemeColorDark1
                     Selection.Font.Color = RGB(255, 255, 255)
                     Selection.Interior.Color = 6299648
                     Selection.Interior.Pattern = xlSolid
                End With

      i = i + 1

    'Save and Close Workbook disable popup alerts (will choose the default button)

On Error GoTo ErrorHandler

    'Set calculation to manual before saving the file
        Application.Calculation = xlCalculationManual

        Application.CalculateBeforeSave = False

    'Save the file and wait for 1 min before continuing to ensure saving is completed
        wb.Save
         Application.Wait (Now + TimeValue("0:01:00"))

    'Close the file and wait for 30 secs before continuing to ensure file is closed
        wb.Close
        Application.Wait (Now + TimeValue("0:00:30"))
        Set wb = Nothing
        DoEvents
    'If a error message shows up after the file has been saved, continue to the next file
    On Error Resume Next

    'Get next file name and loop to the next file
      fileName = Dir

  Loop

'Message Box when tasks are completed
b = Now()

  MsgBox "UPDATE COMPLETE! It took " & Format(b - a, "hh:mm:ss")

  'Reset Macro Optimization Settings
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True
    Application.StatusBar = False
Exit Sub

ErrorHandler:
  'Reset Macro Optimization Settings
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True
    Application.StatusBar = "Ready"
    Application.StatusBar = False
MsgBox "There was an error in " & fileName & ". Please re-run the macro for this and the remaining files!"
End Sub

 

[JLGWhiz]

I don't see any obvious causes for the pop up to display. However, I notice that the caption on then disply says 'Microsoft Visual Basic' instead of 'Microsoft Excel', which would indicate that it is an application generated pop up rather than a user generated one. I have never experienced that particular event before so can't be of much help on this one.
Regards, JLG