Show All Data VBA

[SDave]

Afternoon all,

I have the following code, which up until yesterday was working fine.

It now debugs on ShowAllData - Method "ShowAllData" of object worksheet failed (Runtime error 1004)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    Dim msg As String, _
    orgWs As String
    Dim Ctrl As Office.CommandBarControl
 
    Application.ScreenUpdating = False
 
    For Each ws In Worksheets
    If ws.FilterMode Then ws.ShowAllData
    ws.PageSetup.PrintArea = ""
    Next ws
 
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
    Ctrl.Enabled = True
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=22)
    Ctrl.Enabled = True
    Next Ctrl
 
    Sheets("TM_Feed").Visible = True
    Sheets("TM_Feed").Select
    Range("A3:L1002").ClearContents
    ActiveWindow.SelectedSheets.Visible = False
 
    Sheets("Important_Information").Select
    Range("A1").Select
 
    Application.ScreenUpdating = True
End Sub

Any ideas?!

 

[Andrew Poulsom]

I can duplicate that error if the worksheet is protected.

 

[JackDanIce]

At a guess maybe try:

For Each ws In Worksheets
  If ws.FilterMode = True Then ws.ShowAllData
  ws.PageSetup.PrintArea = ""
Next ws

 

[SDave]

Hi Andrew,

In that case I presume the only way around the problem would be for me to unprotect each sheet before running the code?!

I'll give it a go. It does seem strange however. The sheets themselves were originally protected, and the code ran fine!

 

[sous2817]

Is the box ticked to allow filtering when you protect the sheet? That should solve the problem as well.

 

[SDave]

Hi Sous, yes it is.

Everytime any of the macros are run I begin by stating:

    With Worksheets("People_Data")
    Sheets("People_Data").Unprotect Password:="Batman"

And finish by:

    Sheets("People_Data").Protect Password:="Batman", DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, AllowFiltering:=True, _
    Userinterfaceonly:=True

?!

 

[sous2817]

But doesn't that code just unprotect one sheet, whereas the code that you posted loops through each worksheet in the workbook. So if one of the other worksheets is protected, and allowing autofilter on that worksheet isn't enabled it will error out.

 

[SDave]

Hi Sous,

Yes - it only protects/unprotects one sheet. Having said that each sheet in the workbook allows auto filtering?!

I guess the only way around the problem is to unprotect each sheet, before running the code, and then protect it!

I tested the code without any protection and it works fine... Having said that it worked fine, up until today?!

 

[sous2817]

Well if all of the passwords are the same for each sheet, it's not a huge deal to include the unprotection in your loop.

It is a bit odd that it would work perfectly and then all of a sudden, with no changes made to the workbook, worksheet, or macro things started not working. I don't think I've ever experienced that.

 

[SDave]

It is odd! It's got me stumped.

The password for each sheet is the same.

Having said that there are 3 sheets in the workbook which don't have a password - which shouldn't matter.

How would I go about incorporating the protect/unprotect worksheet function into my loop?!