Activesheet.ShowAllData
Posted by bill.roberts on March 10, 2000 7:01 AM
This command works when data is filtered.
But, bombs out when data is NOT filtered.
I tried to trap the error:
On Error GoTo Next
ActiveSheet.ShowAllData
On Error GoTo 0
But, that does not seem to work.
Can you test (from VBA) for filtered data?
Posted by Celia on March 10, 2000 11:37 PM
Bill
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Celia
Posted by bill.roberts on March 13, 2000 7:53 AM
Celia,
Don't know how the type showed up above but,
I've done that and it didn't work
Perhaps you can find a bug I missed:
------------------------------------------
On Error GoTo Error1
Application.CommandBars.Add (Name:="Phones").Visible = True
Application.CommandBars("Phones").Controls.Add Type:=msoControlButton, Id:= _
2950, Before:=1
Error1:
Application.CommandBars("Phones").Visible = True
On Error GoTo 0
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
------------------------------------------
Because I can sometimes open this file with
no records filtered, my solution was to filter
some programmatically and then ShowAllData.
But that way really sucks!
Posted by Celia on March 13, 2000 8:12 AM
Bill
I don't understand.
I have just copied and pasted the final three lines of your code into a module and it worked whether or not the auto-filter was activated and whether or not the data was filtered.
Celia
Posted by Celia on March 13, 2000 9:03 AM
Bill
The first error trap doesn't look right.
Try :-
On Error Resume Next
On Error GoTo 0
Celia
Posted by bill.roberts on March 13, 2000 9:13 AM
Celia,
I DO NOT doubt you.
In fact, I also copied the code to a new worksheet and produced the same results as you.
So, perhaps you might be able to locate a bug in the code as I originally suspected a problem within.
I say this because there are two TRAPS so close to another.
Bill
------------------------------------------
On Error GoTo Error1
Application.CommandBars.Add (Name:="Phones").Visible = True
Application.CommandBars("Phones").Controls.Add Type:=msoControlButton, Id:= 2950, Before:=1
Error1:
Application.CommandBars("Phones").Visible = True
On Error GoTo 0
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
------------------------------------------
Posted by Celia on March 14, 2000 12:11 AM
Bill
I think the following works :-
Sub Macro1()
On Error Resume Next
Application.CommandBars("Phones").Visible = True
If Application.CommandBars("Phones").Visible = False Then
Application.CommandBars.Add(Name:="Phones").Visible = True
Application.CommandBars("Phones").Controls.Add Type:=msoControlButton, ID:= _
2950, Before:=1
End If
On Error GoTo 0
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
End Sub
Celia
Posted by bill.roberts on March 14, 2000 6:29 AM
Celia,
I think your on to something!!
I'll give it a shot...
Bill
