show all the rows before closing the document

[Guest]

Hi.
How can I prevent to close worksheet when it is used filter (4 example: for
sorting by name)? I need to have always all the rows visible when it is going
to be closed.
Please help me.
Many thanks.
Peter

 

[papou]

Hello Peter
Paste and amend this code with correct sheet name into Thisworkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Worksheets("Sheet1")
If .AutoFilterMode Then
..ShowAllData: End If: End With
Me.Save
End Sub

HTH
Cordially
Pascal

 

[papou]

Need to amend with error handling just in case:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Worksheets("Sheet1")
If .AutoFilterMode Then
On Error Resume Next
..ShowAllData
On Error GoTo 0
End If
End With
Me.Save
End Sub

HTH
Cordially
Pascal

 

[Guest]

Hi
papou.
thank you the code does what I have asked for however one problem occurs.
when I open the worksheet only for information, use filter but accidentaly
change some cell or delete it excel does not ask me whether I want to save
changes or not.


„papou" napísal (napísala):

 

[Bill Renaud]

Peter:

To make sure that Excel prompts you to save ANY changes to the file, delete
the "Me.Save" line in the code previously offered. Change
Worksheets("Data") to be the name of your worksheet. Try the following code
(make sure it is in the "ThisWorkbook" module):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wsData As Worksheet

On Error Resume Next

Set wsData = Worksheets("Data")

With wsData
.ShowAllData 'Needed in case Advanced Filter is used.
.AutoFilterMode = False 'Turn off AutoFilter drop-down arrows.
End With

'Display the unfiltered worksheet while
'the Save dialog box is being displayed.
Application.ScreenUpdating = True
End Sub

 

[Guest]

Thank you both. with some amendments it works well.
Peter

„Bill Renaud" napísal (napísala):