0

I have created a spreadsheet for work and am trying to protect the sheet so that nobody except myself can edit the document. I have set it up so that I can edit the full range of the document. In the protection setting I have ticked the box that says that any users can use auto-filter.

I have the auto-filter set before applying the security so this is not the issue as many forums would suggest. However I am using Excel 2003 whereas the majority of the organization is using Office 2000. I can't help thinking that there is some incompatibility between the two systems. Can anyone advise? VBA macro's aren't really an option as the default setting within the organization is that only signed macros can run but even then they have to be verified by the end user (very annoying for users with a low IT knowledge).

Does anyone know of a way to make this work?

Kara
  • 6,115
  • 16
  • 50
  • 57
Stefan
  • 327
  • 1
  • 5
  • 18

3 Answers3

0

Does the organisation allow to change the default security settings within Excel for yourself and your users?

If not you can easily create your own macro signature. I think its an optional install component to Excel that allows you to create the signature based on your PC or username? This will not have the same standing as a full Microsoft verified signature but depending on the organisation's settings it might allow all your macros to be accepted.

This worked for me a few years ago on Excel 2000 but I still had to have each user change their security settings. I think I also made a macro to automate this change in security settings for each user? If I remember correctly.

Eli
  • 16
0

This is solved now. we are all working on Office 2010.

Stefan
  • 327
  • 1
  • 5
  • 18
  • Stephan, it's good that your problem is solved, but the original question is how to get it to work for Excel 2000 users. The previous accepted answer was correct. Upgrading everyone to Office 2010 is not a solution given the question. – Alain Jun 17 '12 at 16:55
  • Sorry @Alain I've changed the answer – Stefan Jun 17 '12 at 19:02
0

It's not possible without VBA. See http://office.microsoft.com/en-us/excel-help/enable-autofilter-functionality-for-a-protected-worksheet-HA001098270.aspx

By default, the AutoFilter functionality in Excel becomes unavailable when you protect part or all of a spreadsheet. If you use Microsoft Office Excel 2003 or Excel 2002, you can restore that functionality manually. If you use Excel 2000, you need to use a few lines of Microsoft Visual Basic® for Applications (VBA) code.

Use VBA code to protect a worksheet and enable the AutoFilter functionality in Excel 2000

The sample code shown here protects a worksheet (not a workbook) and enables the AutoFilter functionality for that worksheet. The code works only with Excel 2000. This particular sample runs automatically when you open the workbook that contains the protected worksheet. The code also contains a password that you use to unprotect the worksheet.

If you haven't already, start Excel 2000, open the desired workbook, and note the name of the worksheet that you want to protect. On the Tools menu, point to Macro, and then click Visual Basic Editor.

In the Project Explorer, double-click ThisWorkbook.

A new, blank code module opens in the code window.

Copy the following sample code and paste it into the code window:

Private Sub Workbook_Open()
     Sheet1.Protect password:="test", DrawingObjects:=True, _
         contents:=True, Scenarios:=True, _
         userinterfaceonly:=True
     Sheet1.EnableAutoFilter = True End Sub
Alain
  • 26,663
  • 20
  • 114
  • 184