Auto Filter and Protection

C

Carpie

I'm using Excel 2002 and was having trouble using the AutoFilter function when I protected my worksheet even though I chose that option to be available to users. I have since realized that in order for this to work, you have to first put the autofilter on the cells you would like filtered and THEN protect the sheet. You can't protect it and then put the AutoFilter on.

So for me, everything is working fine now. My problem is that the rest of the organization is using Excel 97 and when they enter into the spreadsheet, they are still not able to drop down the boxes of the autofilter. Is this possible or am I screwed?

On a side note, I'm totally NOT A FAN of the new look and changes of these forums. I think they are MUCH harder to use and read.
 
R

Ron de Bruin

Hi

You must use a macro in 97

Private Sub Workbook_Open()
Sheets("yoursheet").EnableAutoFilter = True
Sheets("yoursheet").Protect contents:=True, _
userInterfaceOnly:=True
End Sub

Copy/Paste this in the Thisworkbook module

Right click on the Excel icon next to file on the menubar
Choose view code
The ThisWorkbook module is now active
Paste the code in there
Save/close and reopen the file

This wil protect your sheet but allow you to filter.

--
Regards Ron de Bruin
http://www.rondebruin.nl


Carpie said:
I'm using Excel 2002 and was having trouble using the AutoFilter function when I protected my worksheet even though I chose that
Click to expand...
option to be available to users. I have since realized that in order for this to work, you have to first put the autofilter on the
cells you would like filtered and THEN protect the sheet. You can't protect it and then put the AutoFilter on.
So for me, everything is working fine now. My problem is that the rest of the organization is using Excel 97 and when they enter
Click to expand...
into the spreadsheet, they are still not able to drop down the boxes of the autofilter. Is this possible or am I screwed?
 
G

Gord Dibben

Side note:

Use a news reader like OE or Agent or one of the others and point it to
msnews.microsoft.com

CDO interface is not the greatest.

Gord Dibben Excel MVP
 
C

Carpie

Thanks Ron, it worked like a charm. My only trouble now is that I had a password with the protection and now anyone can unlock it without a password.

Also, I had sharing/tracking enabled. If I enable it now, it goofs up your macro.
 
R

Ron de Bruin

Look in the VBA help Carpie
Select the word Protect in your code and press F1

..Protect Password:="hi", userinterfaceonly:=True

AFAIK with sharing there is no solution
 
C

Carpie

One last question. In the newer versions of Excel, I can specify that although the sheet is protect, users can still enter rows. Is there a line I could enter to the code you provided below that would allow users of Excel 97 this option?
 
D

Dave Peterson

The bad news is that the lines of code you'd use wouldn't work in a shared
workbook--you can't change the protection of a worksheet.

So if you can dump the "sharedness", you can use Ron de Bruin's suggestion. If
you have to have it shared, then I don't think there's a solution that's
acceptible to you.
 
C

Carpie

OK the sharing/tracking is dumped. We'll just have to be more disciplined about exiting the spreadsheet when we are done with it.

So what would be the code to keep the protection in Excel 97, but allow row insertion?
 
D

Dave Peterson

There isn't an equivalent for inserting a row.

You could provide your users a macro that asked them how many rows to insert.

You could either have it unprotect the worksheet, insert the rows and then
protect the worksheet again.

Or you could use that same setting (userInterfaceOnly:=True) and the macro can
do it without unprotecting/reprotecting.

Assuming you've set userinterface to true:

Option Explicit
Sub InsertRows()
Dim myNum As Long

myNum = CLng(Application.InputBox("How many rows after the activecell?", _
Type:=1))

If myNum > 0 Then
On Error Resume Next
ActiveCell.Offset(1, 0).Resize(myNum).EntireRow.Insert
If Err.Number <> 0 Then
MsgBox "Error occurred while inserting rows"
Err.Clear
End If
On Error GoTo 0
End If

End Sub
 
Top