macro won't run

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have placed some buttons on my sheet that run some simple filtering
macros.... I wish to protect the sheet from change, but when it is protected
my buttons will not run the macros... have tried various secuity options
without success.... any ideas?
thanks much
D
 
Dave,

Curious. I've not had trouble with buttons on protected sheets assigned to
macros. Long shot: put a break (F9) in the first executable line of code do
determine if it's at least getting into the code. Then try the button.
 
Dave,

Another thought. Donno of you're doing this or not. I've recently had
trouble withe protecting the sheet in code using UserInterfaceOnly (which
allows the macro to change the sheet, but not the user). I sometimes just
doesn't work (you get an error about trying to change a protected sheet in
spite of UserInterfaceOnly). The workaround in the Microsoft Knowledge base
is to unprotect the sheet in your code, change the stuff, then reprotect.
 
ps......
I get "runtime error 1004"
if I de bug, I get the following window...

Sub MAS()
'
' MAS Macro
' Macro recorded 1/19/2004 by author
'

'
Range("I5:I113").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="y"
Range("A1").Select
End Sub

the part that says "Selection.AutoFilter " is highlighted....
does that help?
D
 
Dave,

Now it sounds as if your table isn't in the range it's selecting. There
should be a heading in I5, and the column containing some y's to autofilter
below that.

I think you need to describe your situation more fully.
 
Are your arrows already applied?

If yes, then you can protect worksheet in code:

Option Explicit
Sub auto_open()
With Worksheets("Sheet1")
.Protect Password:="hi", _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

(you could use the workbook_open even under ThisWorkbook, too.)

That said, worksheet protection is very weak. There's code posted here every
day/week that would unprotect the worksheet.
 
First, I copied|pasted the code. You may want to have the drawingobjects
protected. (sorry).

If you protect a worksheet manually, then your autofilters won't work (xl2002
did add an option for this, though--but not for outlining.

But you can protect a worksheet via code that allows users to use both
autofilter (if the arrows are already applied and outlining (if the outline is
applied).)

And by protecting the worksheet that way, the macro can do it, too.
 
Back
Top