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

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


"Peter KNAP" a écrit dans le message
de news: ...
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

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

"papou" a écrit dans le message
de news: ...
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


"Peter KNAP" a écrit dans le message
de news: ...
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

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):

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

"papou" a écrit dans le message
de news: ...
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


"Peter KNAP" a écrit dans le message
de news: ...
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

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

--
Regards,
Bill Renaud

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

€žBill Renaud" napÃ*sal (napÃ*sala):

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

--
Regards,
Bill Renaud