Autofilter and protection

  • Thread starter Thread starter Jerett
  • Start date Start date
J

Jerett

Again I have hit a minor problem.
Have a macro that autofilters a range on a worksheet to >0
I have an active x control at the bottom of the workbook
that calls the Macro.
The problem is that I need to protect the workbook. I
have unlocked all of the cells and controls that I am
going to allow the user to change. I have even unlocked
the entire column that the macro uses to filter the
sheet. (I will hide this column before protecting the
sheet)
Unprotected the macro works fine. After protecting the
macro I get a runtime error 1004.




Worksheets("Front").Activate
Cells.Select
Selection.PageBreak = xlNone
Range("F28:F332").Select
Range("F28").Activate

Selection.AutoFilter Field:=1, Criteria1:=">0",
Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintPreview
 
Since this code is associated with a control from the control toolbox toolbar,
it's probably behind the worksheet that owns the control.

And I'm betting that that worksheet is different than "Front".

If both those things are true, then this might work:
With Worksheets("Front")
.Select
.ResetAllPageBreaks
.Range("F28:f332").AutoFilter Field:=1, Criteria1:=">0"
End With
ActiveWindow.SelectedSheets.PrintPreview

(and if you're using xl97, make sure you change the .takefocusonclick property
to false (or add activecell.activate to the top of your code). (It's a bug
fixed with xl2k.)

When you refer to Cells or range("F28:f332") in a general module, this
unqualified reference will go back to the activesheet.

But if the code is behind a worksheet, then those ranges belong not to the
activesheet, but to the sheet that owns the code.
 
Back
Top