Application.displayalerts issue

[CMIIandExcel]

Hi all

I am having a problem when using application.displayalerts and it has me scratching my head a bit.

I am using this to supress the alert when deleting a worksheet:

Application.DisplayAlerts = False
Debug.Print Application.DisplayAlerts
sh.Delete
Application.DisplayAlerts = True

When I hover over display alerts it shows true (after the initial change) however the debug prints it as false. I get the alert so it is not working.

to further complicate things if i run the identical code from an excel file located on my USB drive I dont have the problem, but when the excel file is on our network it occurs.

Any thoughts?

I have found a number of references to simular issues but not one with a clear answer

Thanks in advance

Mike

 

[Michael M]

Can't see a reason why it wouldn't work, except if the file on the network drive is either shared or read only !!
Otherwise it should work.

 

[CMIIandExcel]

Hi Michael

Thanks for the reply:

Files are not readonly or shared.

I have done some further tests and the issue seems to lay with where the file which is being manipulated is located i.e.

Excel app - USB drive
Excel File - USB drive
Works fine

Excel app - Network
Excel File - USB drive
Works fine

Excel app - Network
Excel File - Network
Fails

Excel app - USB Drive
Excel File - Network
Fails

I have tried these test on mine and another persons PC and I had the same results.

Any other thoughts?

Mike

 

[CMIIandExcel]

For context here is the full code of the procedure

Private Sub UnlockAndVisibleWorksheets()
Dim sh As Worksheet
  For Each sh In fWorkBook.Worksheets
    DoEvents
    If sh.ProtectContents Or sh.ProtectDrawingObjects Or sh.ProtectScenarios Then
      sh.Unprotect GLOBAL_PASSWORD
    End If
    If sh.Visible <> xlSheetVisible Then
      sh.Visible = xlSheetVisible
      If fOptimise And Not sh.Name = "Validation" Then
        Application.DisplayAlerts = False
        sh.Delete
        Application.DisplayAlerts = True
      End If
    End If
  Next sh
End Sub

All of the code is within a class module

Mike

 

[Michael M]

Any other thoughts?

Not really, don't do much with Class modules though !...

 

[CMIIandExcel]

Thanks Michael,

Anyone else got any ideas? I have found a number of threads relating to it but no answers.

Mike

 

[CMIIandExcel]

Solved

I had numerous application objects and the displayAlerts wasnt always being applied against the application which held the excel file being manipulated in all cases.


odd how the location of the file affected which application object the display alerts was applied against.


Mike