Expert: Bill - 7/8/2008

Question
QUESTION: Hi again Bill,

I've been trying to add some security to the macro code you’ve helped me with during the past week. Unfortunately I'm having a bit of trouble and I thought you might be able to tell me what I'm doing wrong and how to do it right.

As you may recall, 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.  I trigger each of the refresh macros 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.

At the end of this note, I'm including a copy of both macros.  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 macros 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 would work.

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

Thank you.

Stan

---------------- Macros Follows -----------------

Sub CopyRight2Left()

Dim myCell As Range

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("2-Year Data").Select

' ActiveSheet.Unprotect Password:=""
' ActiveSheet.Protect Password:="", UserInterFaceOnly:=True

' Save the current cell location
Set myCell = ActiveCell
Columns("B:AO").Select
Selection.AutoFilter

Sheets("2-Year Data").Range("GG3:GV14060").Copy
Sheets("2-Year Data").Range("I3").PasteSpecial Paste:=xlPasteValues
Sheets("2-Year Data").Range("I3").PasteSpecial Paste:=xlPasteFormats

Sheets("2-Year Data").Range("HA3:HM14060").Copy
Sheets("2-Year Data").Range("AC3").PasteSpecial Paste:=xlPasteValues
Sheets("2-Year Data").Range("AC3").PasteSpecial Paste:=xlPasteFormats

myCell.Select

' ActiveSheet.Protect Password:=""

Application.ScreenUpdating = True
Application.ScreenUpdating = True

End Sub

-------------------------------

Sub FilterCopyRight2Left()

   Dim myCell As Range

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False

   Sheets("2-Year Data").Select

' Save the current cell location
   Set myCell = ActiveCell
'
'   ActiveSheet.Unprotect Password:=""
'  ActiveSheet.Protect Password:="", UserInterFaceOnly:=True

'    Selection.AutoFilter

 Sheets("2-Year Data").Range("GG3:GV14060").Copy
 Sheets("2-Year Data").Range("I3").PasteSpecial Paste:=xlPasteValues
 Sheets("2-Year Data").Range("I3").PasteSpecial Paste:=xlPasteFormats

 Sheets("2-Year Data").Range("HA3:HM14060").Copy
 Sheets("2-Year Data").Range("AC3").PasteSpecial Paste:=xlPasteValues
 Sheets("2-Year Data").Range("AC3").PasteSpecial Paste:=xlPasteFormats

   Columns("B:AO").Select
   Selection.AutoFilter
   Selection.AutoFilter Field:=2, Criteria1:="15:45"

   myCell.Select
'   ActiveSheet.Protect Password:=""
   Application.ScreenUpdating = True
   
End Sub


ANSWER: Try removing UserInterFaceOnly:=True

---------- FOLLOW-UP ----------

QUESTION: I followed your suggestion.  I continue to have the same problem.  There's a copy of the macro that I executed at the end of this message.

The problem seems to be caused by the AutoFilter command. If the Protect command is commented out, the AutoFilter command will execute, and it will either toggle on or off the drop-down box at the top of each column, and everything works perfectly.

But when the Protect Sheet command is inserted into the macro and executes, whenever the AutoFilter command executes and the drop-down boxes appear, the macro buttons no longer work.

Is there a parameter missing from the AutoFilter command?

--------------- Macro ----------------
Sub CopyRight2Left()

   Dim myCell As Range

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False

   Sheets("2-Year Data").Select

   ActiveSheet.Unprotect Password:=""

' Save the current cell location
   Set myCell = ActiveCell
   Columns("B:AO").Select
   Selection.AutoFilter

 Sheets("2-Year Data").Range("GG3:GV14060").Copy
 Sheets("2-Year Data").Range("I3").PasteSpecial Paste:=xlPasteValues
 Sheets("2-Year Data").Range("I3").PasteSpecial Paste:=xlPasteFormats

 Sheets("2-Year Data").Range("HA3:HM14060").Copy
 Sheets("2-Year Data").Range("AC3").PasteSpecial Paste:=xlPasteValues
 Sheets("2-Year Data").Range("AC3").PasteSpecial Paste:=xlPasteFormats
 
   Range("I3:AO94").Select
   Selection.Locked = False
   Selection.FormulaHidden = False

   myCell.Select
   
  ActiveSheet.Protect Password:=""

  Application.ScreenUpdating = True
   Application.ScreenUpdating = True
   
End Sub


Answer
"...Is there a parameter missing from the AutoFilter command?...

No, there are none

Try NOT selecting the cells before doing the filter since you RARELY EVER have to select a cell or range before doing anything with that cell and/or range as I think I told you before when I suggested changes to your copying and pasting.

Ex from

Columns("B:AO").Select
Selection.AutoFilter

to:
Columns("B:AO").AutoFilter

See the common thread? - Select and Selection