Run-time error '5' when I add code to allow filtering on protected sheet.

[StargateFanFromWork]

Love the archives. Do a lot of the main work after consulting them <g>. I
hunted up one of my old messages where I asked about the filtering problems
on a protected sheet. I found the msg and added the code below to my
workbook (from:
http://groups.google.ca/group/micro...gatefan+protect&rnum=8&hl=en#f1fb8069d19cb8f1):

****************************************************************************
*****
Private Sub Workbook_Open()
Dim I As Integer
For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I
End Sub
****************************************************************************
*****

I put the above into the "ThisWorkbook" area below the floating toolbar bar
so that that area looks like this:


----------------------------------------------------------------------------
------------------------------
Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars("LAR Stats [this bar can be moved]").Visible =
True
End Sub
Private Sub Workbook_Deactivate()
' CommandBars disappear (whilst the workbook remains open, but another is in
view)
On Error Resume Next
Application.CommandBars("LAR Stats [this bar can be moved]").Visible =
False
End Sub
Private Sub Workbook_Open()
Dim I As Integer
For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I
End Sub
----------------------------------------------------------------------------
------------------------------

After enabling the macros upon opening this file, I get the run-time error
'5':

"Microsoft Visual Basic
Run-time error '5':
Invalid proceudre call or argument"

If I click END on the error box, the floating toolbar still comes up and I
can still filter even though sheet is protected but I'd like to know what to
do to remove this error.

Thanks. D

 

[Doug Glancy]

StargateFanFromWork,

I took a look at your code and it works for me. Error 5 would be generated
by trying to set a non-existent toolbar to visible, but you've preceded that
code with "On Error Resume Next", so that's not it. Is there other code
where you try to do something to the toolbar which is not preceded by "On
Error"?

When you get the error and you choose "Debug" instead of "End" what line of
code is highlighted?

Sorry, I don't have an answer, but it's been a bit since you posted, so
thought I'd at least share what I can.

Doug

 

[StargateFanFromWork]

StargateFanFromWork,

I took a look at your code and it works for me. Error 5 would be
generated by trying to set a non-existent toolbar to visible, but you've
preceded that code with "On Error Resume Next", so that's not it. Is
there other code where you try to do something to the toolbar which is not
preceded by "On Error"?

When you get the error and you choose "Debug" instead of "End" what line
of code is highlighted?

Sorry, I don't have an answer, but it's been a bit since you posted, so
thought I'd at least share what I can.

Doug

Doug, thanks so much for your reply. I had a hard time with this one.
Since you've posted, I've kept coming back to the file to see what could be
the matter. Today a possible problem occurred to me. And it was the case.
I have 3 worksheets in all in this workbook and I had commands turning off
the toolbar in the other 2 sheets which was creating a conflict, I imagine.
Unlike other workbooks, it doesn't matter if the toolbar shows up in all the
sheets. The other 2 are just instruction sheets that the users will rarely
use so the toolbar can stay. End of problem <phew>. So I'm writing to
report back re this.

But I did run into behaviour that I'm not keen on and haven't seen before.
When I open this workbook, it eventually flips over to the last worksheet in
the book instead of the main one. The main sheet is called "LAR stats" and
that's where the focus should be after opening.

Is there any way to change this behaviour? I've just run a few trials and
it always opens up this way no matter that I save and close in the main
sheet each time.

p.s., this is an Excel doct. saved in a DOCSopen environment, which
shouldn't be an issue at all, but thought I'd mention it. Thanks. D

Love the archives. Do a lot of the main work after consulting them <g>.
I
hunted up one of my old messages where I asked about the filtering
problems
on a protected sheet. I found the msg and added the code below to my
workbook (from:
http://groups.google.ca/group/micro...gatefan+protect&rnum=8&hl=en#f1fb8069d19cb8f1):

****************************************************************************
*****
Private Sub Workbook_Open()
Dim I As Integer
For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I
End Sub
****************************************************************************
*****

I put the above into the "ThisWorkbook" area below the floating toolbar
bar
so that that area looks like this:


----------------------------------------------------------------------------
------------------------------
Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars("LAR Stats [this bar can be moved]").Visible =
True
End Sub
Private Sub Workbook_Deactivate()
' CommandBars disappear (whilst the workbook remains open, but another is
in
view)
On Error Resume Next
Application.CommandBars("LAR Stats [this bar can be moved]").Visible =
False
End Sub
Private Sub Workbook_Open()
Dim I As Integer
For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I
End Sub
----------------------------------------------------------------------------
------------------------------

After enabling the macros upon opening this file, I get the run-time
error
'5':

"Microsoft Visual Basic
Run-time error '5':
Invalid proceudre call or argument"

If I click END on the error box, the floating toolbar still comes up and
I
can still filter even though sheet is protected but I'd like to know what
to
do to remove this error.

Thanks. D

 

[StargateFanFromWork]

Oops, I forgot to mention, it's XL2K on WinXP.

 

[StargateFanFromWork]

To re-cap, the run-time error was fixed by removing the code to allow
filtering which seemed to interfere with the toolbar commands. Since the
filtering was only allowed once before any other command re-protected the
sheet, it wasn't the solution for this workbook. But I'm returning to the
filtering issue once more: how to enable filtering more than once so that
it still works after any toolbar command selected from the toolbar
re-protects the sheet.

I think I've not wrapped my brain around this problem properly. 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 Autpen()
With Worksheets("Sheet1")
.EnableAutofilter = True
.Protect UserInterfaceOnly:= True
End With
End Sub

The Autpen prompted an idea. Both codes resemble each other yet are put
in two different spots. My current Autpen contains all the code for the
rather large floating commandbar. And each command has this at the end to
lock up the sheet at the finish of each command's task:
ActiveSheet.Protect ' place at end of code
This works very well. My workbooks will stand a greater chance of not being
messed up or at least not as easily or as quickly.
[The commands have an unprotect code at the beginning of each so that
sorting, etc., can be done then this at the end protects the sheet again, in
other words.]

Is there code one can place after that "ActiveSheet.Protect" line in code so
that the user is allowed to manually filter the sheet without affecting
nullifying the ActiveSheet.Protect? That's probably the big question! <g>

TIA.