Cant use Filter or Hide/ Unhide rows after Applying Macro

[JIMMY024]

Hi
I am trying to write a simple macro to make repetitive task much easier.
Issue I have is that when I run the Macro I cant use the filters or hide/ unhide features.

I don't have this issue prior to running the Macro.

The spread sheet itself if Password protected but I thought I had written correctly to unlock and lock it again. Is it when the code is locking the spread sheet again that these functions are being disabled???

Also someone mentioned I should consider writing an Error handling code. What would that look like.? Are there general ones out there that would not leave the spread sheet open if it encountered an error??

To keep it simple this is the code im working on.

Sub Pending()
'
' Pending Macro
'

'
ActiveSheet.Unprotect Password:="Jim"
Columns("C:E").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Range("$A$7:$AJ$84").AutoFilter Field:=13, Criteria1:="Pending"
Range("A10").Select
ActiveSheet.Protect Password:="Jim"
End Sub



Thanks
Jim

 

[Fishboy]

Hi
I am trying to write a simple macro to make repetitive task much easier.
Issue I have is that when I run the Macro I cant use the filters or hide/ unhide features.

I don't have this issue prior to running the Macro.

The spread sheet itself if Password protected but I thought I had written correctly to unlock and lock it again. Is it when the code is locking the spread sheet again that these functions are being disabled???

Also someone mentioned I should consider writing an Error handling code. What would that look like.? Are there general ones out there that would not leave the spread sheet open if it encountered an error??

To keep it simple this is the code im working on.

Sub Pending()
'
' Pending Macro
'

'
ActiveSheet.Unprotect Password:="Jim"
Columns("C:E").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Range("$A$7:$AJ$84").AutoFilter Field:=13, Criteria1:="Pending"
Range("A10").Select
ActiveSheet.Protect Password:="Jim"
End Sub



Thanks
Jim

Hi Jim,

The problem you are experiencing is due to the fact that there are various options as to what is or is not permitted when protecting a sheet. When manually protecting a sheet this is achieved by ticking the various boxes of what you want to allow, however in VBA you need to specify what you want. Whilst you don't need to include each and all options in every macro, Excel will assume that anything you omit will not be allowed.

If you want to be able to use filters and to hide / unhide rows you need to include this in your code. Please note you will initially want to manually turn all the desired filters on before the sheet is protected, as even if allowed once protected you cannot turn the filters on / off just by clicking. You can use the filters which have been applied, but the filter button itself on the data tab will be greyed out. Please see my updated code below:

Sub Pending()
ActiveSheet.Unprotect Password:="Jim"
Columns("C:E").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Range("$A$7:$AJ$84").AutoFilter Field:=13, Criteria1:="Pending"
Range("A10").Select
ActiveSheet.Protect Password:="Jim", AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowFiltering:=True
End Sub

AllowFormattingColumns will allow you to hide / unhide columns
AllowFormattingRows will allow you to hide / unhide rows
AllowFiltering will allow you to use the filters

I hope this helps.

 

[JIMMY024]

Yes indeed.
I have applied your solution and it works like a charm..
Thank you so much for the help and the explanation also. Its good to know why that was happening.

Cheers

 

[Fishboy]

Yes indeed.
I have applied your solution and it works like a charm..
Thank you so much for the help and the explanation also. Its good to know why that was happening.

Cheers

Happy to help