Problems with AutoFilter and Protect Sheet

I have a file containing data, formulas and derived data. Essentially the formulas use the data to produce the derived data values. Macros are triggered by clicking one of four buttons - one to refresh detail views, one to refresh summary views, and two others that simply move the page view either to the left or to the right. I'm included a copy of one of the two macros that refresh the data at the end of this note.

Before I commented out the Protect commands, what I was trying to accomplish was to prevent users from being able to change the actual data and from also seeing the formulas. In preparation to use these macros, I used the Format/Cells/Protection selection in Excel to explicitly lock and/or hide the appropriate columns. Next I used the Tools/Protection/Protect Sheet selection to protect the worksheet. Afterwards I started testing the macros by clicking the buttons I set up on the sheet.

The macros that shift the page views from left to right and visa-versa can be used right up to the point when I start experiencing problems triggering the other macros. These are CopyRight2Left and FilterCopyRight2Left, the ones that specifically refresh the derived data values and Protect or UnProtect the worksheet. Then the macros buttons I set up on the sheet could not be clicked. I had to manually UnProtect the worksheet in order to use the buttons again. But as soon as the sheet becomes protected again using the macros that refresh the derived data values, the same problem occurred. None of the macro buttons on the worksheet would work.

Please let me know what I need to change to get things working properly.

Thank you.

---------------- Macro Follows -----------------

Please Login or Register  to view this content.

I've been trying to add some security to my macro code. Unfortunately I'm having a bit of trouble and I need some help.

The file that I'm working with has a worksheet containing data, formulas, and derived data. Essentially the formulas use the data to produce the derived data values. The macros are used to refresh the derived data values. There are two macros that I use by clicking one of two buttons - one to refresh detail view, one to refresh the summary view. I use the AutoFilter command in the first macro to toggle off the summary view created by the second macro.

Attached is a copy of both macros.

Please Login or Register  to view this content.

As is, the macro code without the Protect commands works perfectly. That's because I've commented out the various Protect commands. As part of the macro code, the Protect commands were meant to prevent users from being able to change the actual data and from also seeing the formulas.

In preparation to use these macros, I used the Format/Cells/Protection selection in Excel to explicitly lock and/or hide the appropriate columns. Next I used the Tools/Protection/Protect Sheet selection to protect the worksheet. I activate these macros by clicking buttons I set up on the sheet.

The first few times I use the macros, they work as intended. But it seems that after any of them lock the sheet, I cannot click on any of the macro buttons I’ve set up on the sheet. I have to go and manually UnProtect the worksheet in order to use the buttons again. But as soon as the sheet becomes protected again using the macros, I’m faced with exactly the same problem. None of the macro buttons on the worksheet work.

Please let me know what I need to change to get things working properly.

Thank you.

What buttons have you used/ If they are from the Controls toolBox (ActiveX) then have you exited Design View?

Protecting the sheet shouldn't stop you from clicking them.