show all data issue

[Belinda7237]

I am having filter issues in a shared workbook. I am thinking that I can
solve this by having a code like this:

ActiveSheet.ShowAllData

inserted so that when each person opens the shared workbook it will clear
current filters for that user.

can i use this code in a shared workbook with multiple people in it and
filtering at the same time? If so, where do i put this code? i am only
familar with creating control buttons and are not familar with where to put
code on open for excel?

Thanks

 

[paul.robinson]

Hi
In the Visual Basic Editor double click the "ThisWorkbook" part of
your project (beow the sheet names and before your code modules).
Paste in this sub.

Private Sub Workbook_Open()
'Remove any filter present on the Activesheet
With Activesheet
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
End With
End sub

This will clear the advanced filter and any other filter dropdowns. If
you want to apply to a particular sheet replace ActiveSheet with
Worksheets("MySheetName").

It might also be a good idea to put the same code in the before close
event too, just to be sure.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Remove any filter present on the Activesheet
With Activesheet
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
End With
End sub

Note that the Open and BeforeClose macros only go in the Thisworkbook
module. you can see other macros in here by selecting Workbook instead
of General at the top left dropdown menu and viewing/inserting the
macros listed in the top right dropdown menu.

regards
Paul

 

[Belinda7237]

i want the drop downs to stay but i want the potential to have one of the
columns filtered to refresh to showing all. And i have taken all of my
advanced filters away due to the shared workbook and just have regular
filters.