Filtering protected sheets (again).

[StargateFanFromWork]

Can we enable filtering more than once so that it still works even after any
toolbar command selected from the floating commandbar re-protects the sheet?

I searched the archives yet again and found 2 messages again both with code
kindly provided by Tom Ogilgy:

Sub AllowFilter()
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterFaceOnly:=True
End Sub

Sub Auto_Open()
With Worksheets("Sheet1")
.EnableAutofilter = True
.Protect UserInterfaceOnly:= True
End With
End Sub

The Auto_Open prompted an idea. I've been using a variation of the first
code above in the ThisWorkbook so that filtering is enabled upon opening the
workbook. Yet the moment any of the commandbar codes are pressed the
filtering then promptly is disabled since each has an "ActiveSheet.Protect"
code at the end. [A corresponding unprotect code is put at the beginning of
each macro so that sorting, etc., can be done. These two codes must
remain.]

So the goal is to find something that can be placed after that
"ActiveSheet.Protect" line in each macro so that the user is allowed to
manually filter the sheet without nullifying the ActiveSheet.Protect. That
would be the crux of the matter, I'm guessing -- whether or not that can be
done? TIA.

 

[Norman Jones]

Hi StargateFen,

Try removing the unprotect / re-protect instructions from the
"commandbar codes".

If you use the AutoOpen code from Tom. the sheet will remain
protected but will enable VBA interaction with the sheet. For the
user the sheet is protected but Vba is able to to handle the sheet
as if it were not protected.

 

[StargateFanFromWork]

Hi StargateFen,

Try removing the unprotect / re-protect instructions from the
"commandbar codes".

Okay-doke. Sounds good. Will do this.

If you use the AutoOpen code from Tom. the sheet will remain
protected but will enable VBA interaction with the sheet. For the
user the sheet is protected but Vba is able to to handle the sheet
as if it were not protected.

Sounds intriguing. I have a couple of questions re this ...

1) Will the "With ActiveSheet" work here as I've put it, instead of the
original 'With Worksheets("Sheet1")' so I don't name the sheet in case the
user changes the name?

Sub Auto_Open()
With ActiveSheet
.EnableAutofilter = True
.Protect UserInterfaceOnly:= True
End With
End Sub


2) Also, any problem with putting the above "With" ... "End With" at the
top of my commandbars code, as seen below:

(This bit below is just below the "Option Explicit" part.)

******************************************************************************************
Sub Auto_Open()

On Error Resume Next



With ActiveSheet
.EnableAutofilter = True
.Protect UserInterfaceOnly:= True
End With




Application.CommandBars(myName).Delete

Set myBar = Application.CommandBars.Add(myName)
With myBar
'this "Protection" line of code _below_ removes the X in the upper
right-hand corner of bar
.Protection = msoBarNoChangeVisible
'this "Protection" line of code _above_ removes the X in the upper
right-hand corner of bar
.Position = msoBarFloating
' Left and Top positions for the bar, in pixels.
.Left = 125
.Top = 138
.Visible = True
.Enabled = True
' 1. Show all data. ...
******************************************************************************************

Before I go messing around with the miles of code, thought I'd check to see
if I had everything okay first.

Thanks! D

---
Regards,
Norman

Can we enable filtering more than once so that it still works even after
any toolbar command selected from the floating commandbar re-protects the
sheet?

I searched the archives yet again and found 2 messages again both with
code kindly provided by Tom Ogilgy:

Sub AllowFilter()
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterFaceOnly:=True
End Sub

Sub Auto_Open()
With Worksheets("Sheet1")
.EnableAutofilter = True
.Protect UserInterfaceOnly:= True
End With
End Sub

The Auto_Open prompted an idea. I've been using a variation of the first
code above in the ThisWorkbook so that filtering is enabled upon opening
the workbook. Yet the moment any of the commandbar codes are pressed the
filtering then promptly is disabled since each has an
"ActiveSheet.Protect" code at the end. [A corresponding unprotect code
is put at the beginning of each macro so that sorting, etc., can be done.
These two codes must remain.]

So the goal is to find something that can be placed after that
"ActiveSheet.Protect" line in each macro so that the user is allowed to
manually filter the sheet without nullifying the ActiveSheet.Protect.
That would be the crux of the matter, I'm guessing -- whether or not that
can be done? TIA.

 

[Norman Jones]

Hi StargateFan,

'----------------
Sounds intriguing. I have a couple of questions re this ...

1) Will the "With ActiveSheet" work here as I've put it, instead of the
original 'With Worksheets("Sheet1")' so I don't name the sheet in case the
user changes the name?
'----------------

Probably not. If you have concerns relating to the the
renaming of the sheet, use the worksheets CodeName
rather than its name; the code name will remain constant
irrespective of changes to the name. If you look in the VBE
Project Explorer window, you will see eacg sheet listed
with its code name and its Excel interface name in parentheses.
For futher information, see 'code name' in VBA help,

The relevant syntax would be like:

'=============>>
Public Sub Auto_Open()
With Sheet1
.EnableAutoFilter = True
.Protect UserInterfaceOnly:=True
End With
'<<=============

'------------------
2) Also, any problem with putting the above "With" ... "End With" at the
top of my commandbars code, as seen below:

(This bit below is just below the "Option Explicit" part.)

****************************************************************************************** Sub Auto_Open() On Error Resume Next With ActiveSheet .EnableAutoFilter = True .Protect UserInterfaceOnly:=True End With Application.CommandBars(myName).Delete Set myBar = Application.CommandBars.Add(myName) With myBar 'this "Protection" line of code _below_ removes the X in the upper right-hand corner of bar .Protection = msoBarNoChangeVisible 'this "Protection" line of code _above_ removes the X in the upper right-hand corner of bar .Position = msoBarFloating ' Left and Top positions for the bar, in pixels. .Left = 125 .Top = 138 .Visible = True .Enabled = True ' 1. Show all data. ...End Sub'------------------It should be unnecessary to repeat the protection code.As previously indicated, using the userInterfaceOnlyparameter renders the sheet protected for the user,but allows VBA maniipulation---Regards,Norman

 

[StargateFanFromWork]

Hi StargateFan,

'----------------
Sounds intriguing. I have a couple of questions re this ...

1) Will the "With ActiveSheet" work here as I've put it, instead of the
original 'With Worksheets("Sheet1")' so I don't name the sheet in case the
user changes the name?
'----------------

Probably not. If you have concerns relating to the the
renaming of the sheet, use the worksheets CodeName
rather than its name; the code name will remain constant
irrespective of changes to the name. If you look in the VBE
Project Explorer window, you will see eacg sheet listed
with its code name and its Excel interface name in parentheses.
For futher information, see 'code name' in VBA help,

Wow. I didn't know that. All these years and I'd never figured that out.

The relevant syntax would be like:

'=============>>
Public Sub Auto_Open()
With Sheet1
.EnableAutoFilter = True
.Protect UserInterfaceOnly:=True
End With
'<<=============

'------------------
2) Also, any problem with putting the above "With" ... "End With" at the
top of my commandbars code, as seen below:

(This bit below is just below the "Option Explicit" part.)

******************************************************************************************
Sub Auto_Open() On Error Resume Next With ActiveSheet .EnableAutoFilter
= True .Protect UserInterfaceOnly:=True End With
Application.CommandBars(myName).Delete Set myBar =
Application.CommandBars.Add(myName) With myBar 'this "Protection" line of
code _below_ removes the X in the upper right-hand corner of bar
.Protection = msoBarNoChangeVisible 'this "Protection" line of code
_above_ removes the X in the upper right-hand corner of bar .Position =
msoBarFloating ' Left and Top positions for the bar, in pixels. .Left =
125 .Top = 138 .Visible = True .Enabled = True ' 1. Show all
data. ...End Sub'------------------It should be unnecessary to repeat the
protection code.As previously indicated, using the
userInterfaceOnlyparameter renders the sheet protected for the user,but
allows VBA maniipulation---Regards,Norman

So far, so good. I had some trouble at first. The sheet was being left
unprotected after using each button even though I went through and took out
all the unprotect/protect codes. Don't know what the trouble was. I ended
up dumping in "ActiveSheet.Protect UserinterfaceOnly:=True" before each "End
Sub" for now and that seems to be keeping things locked. Again, I'll remove
all those in a copy over the weekend and see if I can determine what's going
on. But it can't hurt to have that code there, I'm thinking.

Again this ng has saved my bacon. Thanks, Norman! Once I'm sure I've
figured out how to make this work successfully, I'll start systematically
switching over the code of all my workbooks that have a floating commandbars
to this better system. I'm also tackling an important project next week in
which this better approach will be a godsend. All the shop guys will need
to access this new Excel workbook so keeping full functionality while
keeping the book locked up will be critical with such a large number of
users.

Cheers and Happy Easter to those that celebrate.