Why can't I use filters on a protected Excel sheet ? 1

[LeighAnne]

I have a spreadsheet of information that I would like people to be able to view in Excel.

I want to allow other users to query the spreadsheet using filters at will, however, I've just found out that when you protect a sheet filtering doesn't work !

I don't want to password protect the docuument as there are some areas of the spreadsheet that I need user input.

I need a way to get around this if possible, any ideas ?

Help !

 

[cheerio]

If you protect a worksheet you cannot change its structure. In particular you cannot hide rows.

Now the autofilter works by hiding rows. Therefore it does not work.

Ken

 

[rmikesmith]

LeighAnne,

Ken is right...Microsoft, in their infinite wisdom really didn't put much thought into what a developer would want to make available to the user while at the same time protecting a worksheet.

You can try the following, although it is not an ideal solution. It will work to protect your sheet against at least inadvertent changes. Place the following code into the code module of the worksheet you want to protect. Whenever the user attempts to change the selected cell, he/she will get a warning. For extra protection you can lock the VBA Project for viewing with a password.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  On Error Resume Next
  Application.EnableEvents = False
  Range("A1").Select
  Application.EnableEvents = True
  MsgBox "This worksheet cannot be edited", vbOKOnly + vbExclamation, "Warning"
End Sub

If you need additional help setting this up post back. If your workbook doesn't contain sensitive data, send me a copy and I'll implement the above setup.

Regards,
M. Smith

 

[rmikesmith]

LeighAnne,

In my haste I forgot to post my email address:

rmikesmith@earthlink.net

Regards,
Mike

 

[LeighAnne]

Thanks Mike,

Great lateral thinking. An excellent solution that I would never have thought of myself !

Leigh - Anne