Remove filter and default active cell to A1 on close?

[laxster]

Hi all,

You guys have been a huge help with my Excel conundrums the past few days!

Is there a way I can use VBA to automatically remove filters on a sheet and have it default the active cell to A1 when a sheet is saved and closed so that the next person to open it sees it "fresh"?

 

[HaHoBe]

Hi, laxster,

first of all no sheet can be saved or closed, only a workbook can.

Use the Workbook_BeforeClose(Cancel As Boolean)-event in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveSheet
    'check for any Autofilter
    If .AutoFilterMode Then .UsedRange.AutoFilter
End With
Range("A1").Select
'save the alteration to the workbook
ThisWorkbook.Save
End Sub

Ciao,
Holger

 

[laxster]

Thank Holger!

A couple of questions: first, it seems to take the AutoFilter off completely. Is there a way to leave the Autofilter on the worksheet but just reset the values to "all" so the filter is active, but the filters applied to the worksheet are removed?

Second, is it possible for this to function on a protected sheet? I have certain areas protected that users shouldn't touch, and when a user tries to use this they get an error message.

I appreciate your help!

 

[Brianwarnock]

I don't know the answer about the protected sheet as I have never done this , but help shows that you want showalldata.
Then try it.

Brian

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveSheet
    .Showalldata
End With
Range("A1").Select
'save the alteration to the workbook
ThisWorkbook.Save
End Sub

 

[laxster]

Thank you; that works perfectly!

 

[HaHoBe]

Hi, laxster,

use the Workbook_Open-Event to protect the worksheets and use the additional Parameter UserUnterfaceOnly:=True which will allow macro code to run oin the protected sheets. As this option is not saved with the workbook it has to be set on each opening (with activates macros).

Ciao,
Holger

 

[laxster]

Well, I added in the following code:

ActiveSheet.Unprotect Password:="TNT", UserInterfaceOnly:=True, AllowFiltering:=True

and the reverse when the workbook closes:

ActiveSheet.Protect Password:="TNT", UserInterfaceOnly:=True, AllowFiltering:=True

but this has been causing OnTime to fail. How can I continue to let OnTime to work while using this bit of code to unprotect the sheet as needed?

 

[Brianwarnock]

I should really wait for Holger as I have no experience here, but I did not thing from his post that you had to do anything on closure, I would remove that code and try again whilst waiting for his response.

Brian

 

[laxster]

I removed the reverse as advised (it appears to be redundant) but onTime still fails. I'd think that onTime and this function should be able to co-exist together. :/

 

[Brianwarnock]

The timing code was provided for you by Holger in another thread and its outside my knowledge/experience, and whilst i agree it apparently should work I think we are going to need Holger or somebody similarly experienced to help.

Brian

 

[HaHoBe]

Hi, laxster,

maybe I haven´t been very clear on this one but AFAIK you only need to set the Protect-Method in the Workbook_Open-Event (as it will never be saved when the workbook is closed, and using Unprotect with UserInterfaceOnly isn´t what should be used):

Private Sub Workbook_Open()
    Sheet1.Protect Password:="TNT", UserInterfaceOnly:=True, AllowFiltering:=True
End Sub

As I do not know if there is more than one sheet in the workbook I try to use the Codename which is displayed in the project explorer instead of the displayed name which can easily be altered and lead to a run-time error.

Again, no code for the protection is needed in the Workbook_BeforeClose-Event (except those lines for the timing).

Ciao,
Holger

 

[laxster]

I do seem to have one issue left. When I close out the workbook, I get a runtime error '1004' when a filter has been applied. Upon closing the workbook, I'd like for the filter to be "un-filtered" but not removed completely, so I used a combination of both your code and Brian's code. Here's what it looks like:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    SetBack
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Range("A2").Select
'save the alteration to the workbook
ThisWorkbook.Save
End Sub

And the line that is highlighted to indicate something wrong is ActiveSheet.ShowAllData

What am I doing wrong with this?

Again, I appreciate you guys walking me through this! It's challenging, but I really feel like I am learning a lot, too.

 

[Brianwarnock]

Works fine in my test, but I don't have Setback in whats that?

Brian

 

[HaHoBe]

Hi, laxster,

I´m out of any thread from your side in the future and this one in particular - just another proc causing trouble you didn´t care to mention.

Bye
Holger

 

[laxster]

Hi Holger,

I don't quite understand what you're getting at. What didn't I care to mention? I've shared everything possible and I'm really trying to learn and understand what's going on. You've been of great assistance to me, so I'm not understanding where you are coming from on that last post. If I've left anything out, I apologize.

 

[Brianwarnock]

I don't follow Holger either, I have now discovered Setback comes from the Timeout thread , from Holger's own offering.

Perhaps the combination of 2 threads is confusing but they did start out as separate issues, just that they used the same events.

Brian

 

[Brianwarnock]

Ok read Holger's work on the other thread and then had a play.
The important information is the wait for the time of Datend ie 5 mins in Holger's example, after saving the workbook following the changes to the close event. I actually ran the startproc but not sure if that is needed, but anyway all works now. When I just coded the close event and tried to close thw workbook I got the 1004 eror.

Brian

 

[laxster]

*Phew* I thought I was going crazy! I tried rebuilding the entire sheet as well from scratch and was still getting the same error.

I appreciate Holger's help; I hope he has an idea of what's going on here, because I'm stumped and am not sure how to fix this. The issue comes down to between that timing module, the above code, and protection. If the worksheet is completely unprotected, everything works as designed. The problem is, I can't have this sheet unprotected!

 

[Brianwarnock]

Did you change your code in the open event as per Holger's post 11

Private Sub Workbook_Open()
Sheet1.Protect Password:="TNT", UserInterfaceOnly:=True, AllowFiltering:=True
End Sub

You had the open as unprotect, that has no parameters and is not what you want.
I have it working perfectly with the sheet protected xcept for my filter, it times out or I can close it and filtering is reset.

I've learnt alot with this, pity I'm retired and will never use it.


Brian