Saving as dialog box. Application.DisplayAlerts not working

[Serafin54]

Hello,

can someone help me with suppressing the "saving as \\xyz\branch\sh\Analyst Use Only\UserLog.xls" dialog box that appears when saving?

I have attempted to add Application.DisplayAlerts=FALSE and Application.DisplayAlerts=True to the code but it doesn't seem to make any difference. Thank you.

'Create UserLog and record metrics
    Dim filePath As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long
    Application.ScreenUpdating = False

    ' Define the file path
    filePath = "\\xyz\branch\sh\Analyst Use Only\UserLog.xlsx"
    
    ' Check if the file exists
    If Dir(filePath) = "" Then
        ' Create a new workbook if the file doesn't exist
        Set wb = Workbooks.Add
        wb.SaveAs filePath
        wb.Close
    End If
    
    ' Open the UserLog workbook
    Set wb = Workbooks.Open(filePath)
    
    ' Add a new worksheet named "UserLog" if it doesn't exist
    On Error Resume Next
    Set ws = wb.Sheets("UserLog")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
        ws.Name = "UserLog"
        ' Add headers
        With ws
            .Cells(1, 1) = "Username"
            .Cells(1, 2) = "Date and Time"

        End With
    End If
    
    ' Find the last used row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Unprotect sheet and write username and current date and time to the next row
    With ws
        .Unprotect "incorrect"
        .Cells(lastRow + 1, 1) = Environ("Username")
        .Cells(lastRow + 1, 2) = Now

    End With
    
    ' Autofit all columns
    ws.Columns.AutoFit
    
    'Re-protect sheet
    ws.Protect "incorrect", UserInterfaceOnly:=True
    
    ' Save the workbook
    wb.Save
    
    ' Close the workbook
    
    wb.Close
    Application.ScreenUpdating = True
    ' Release memory
    Set wb = Nothing
    Set ws = Nothing

Application.ScreenUpdating = True

'Process Complete messaging
    MsgBox "Process Complete."

 

[DanteAmor]

dialog box that appears when saving?

What the message says?

Application.DisplayAlerts=True

Try at the beginning of the code:

Application.DisplayAlerts=False

 

[rlv01]

In addition to @DanteAmor 's recommendation, I notice that when a filepath does not exist, you are creating, saving, closing, then reopening the new workbook. You could simplify by not closing it.

    ' Check if the file exists
    If Dir(filePath) = "" Then
        ' Create a new workbook if the file doesn't exist
        Set wb = Workbooks.Add
        Application.DisplayAlerts = False
        wb.SaveAs filePath
        'wb.Close                                      'no need to close
        Application.DisplayAlerts = True
    Else
        ' Open the UserLog workbook
        Set wb = Workbooks.Open(filePath)
    End If
    
    ' Add a new worksheet named "UserLog" if it doesn't exist
    On Error Resume Next
    Set ws = wb.Sheets("UserLog")
    On Error GoTo 0

 

[Serafin54]

What the message says?



Try at the beginning of the code:

Application.DisplayAlerts=False

it was a message box stating "Saving As" followed by the file name.

I did try the application.displayalerts route but it didn't stop it from appearing.

 

[Serafin54]

In addition to @DanteAmor 's recommendation, I notice that when a filepath does not exist, you are creating, saving, closing, then reopening the new workbook. You could simplify by not closing it.

    ' Check if the file exists
    If Dir(filePath) = "" Then
        ' Create a new workbook if the file doesn't exist
        Set wb = Workbooks.Add
        Application.DisplayAlerts = False
        wb.SaveAs filePath
        'wb.Close                                      'no need to close
        Application.DisplayAlerts = True
    Else
        ' Open the UserLog workbook
        Set wb = Workbooks.Open(filePath)
    End If
   
    ' Add a new worksheet named "UserLog" if it doesn't exist
    On Error Resume Next
    Set ws = wb.Sheets("UserLog")
    On Error GoTo 0

thank you. i will give this a try.

 

[Serafin54]

it was a message box stating "Saving As" followed by the file name.

I did try the application.displayalerts route but it didn't stop it from appearing.

this unfortunately didn't work either and actually seemed to slow the code down. I think I might just live with the notification as the folder is locked and will just add a footnote to the add-in itself to inform the users of this notification. I appreciate the help though. Just not sure how much time i want to spend skinning a cat when it doesn't interfere with the end result. Just a nuissance more than anything really.

 

[MARK858]

Application.DisplayAlerts=False only hides Excel alerts, if it is a Windows alert then it won't be hidden

 

[Serafin54]

Application.DisplayAlerts=False only hides Excel alerts, if it is a Windows alert then it won't be hidden

That might just be the issue. Didn't even consider that. Thank you!