Using VBA to Protect sheet(s) with Autofilter


P

Phoenix

Hi!

I'm using this commands in VBA (Excel 2000):

Sub Auto_Open()
With Worksheets("Sheet1")
.Protect Password:="pass", userinterfaceonly:=True
.EnableAutoFilter = True
End With

End Sub


....to use autofilter when the sheet1 is protected. I also created th
same VBA commands to do the same in sheet2:

Sub Auto_Open()
With Worksheets("Sheet2")
.Protect Password:="pass", userinterfaceonly:=True
.EnableAutoFilter = True
End With

End Sub


It works fine until I close and reopen this workbook. Then there is a
error message "Ambiguous name detected: Auto_Open"

It works fine with only sheet1 protected, but not with two or more??

What can be the problem
 
Ad

Advertisements

N

Nick HK

Phoenix,
Sub Auto_Open() is run when the workbook is opened. Therefore you only need
one such routine, in which you loop through all the worksheets, setting the
Protection and other properties as desired.
You can't "Open" a worksheet, only the entire book, so this makes sense.

NickHK


|
| Hi!
|
| I'm using this commands in VBA (Excel 2000):
|
| Sub Auto_Open()
| With Worksheets("Sheet1")
| Protect Password:="pass", userinterfaceonly:=True
| EnableAutoFilter = True
| End With
|
| End Sub
|
|
| ...to use autofilter when the sheet1 is protected. I also created the
| same VBA commands to do the same in sheet2:
|
| Sub Auto_Open()
| With Worksheets("Sheet2")
| Protect Password:="pass", userinterfaceonly:=True
| EnableAutoFilter = True
| End With
|
| End Sub
|
|
| It works fine until I close and reopen this workbook. Then there is an
| error message "Ambiguous name detected: Auto_Open"
|
| It works fine with only sheet1 protected, but not with two or more??
|
| What can be the problem?
|
|
| ------------------------------------------------
|
|
|
 
P

Phoenix

Thanx Nick HK, but I need a subroutine for each sheet also? What wil
that be? "sub workbook_open" ?

Phoeni
 
P

Phoenix

Seems like this was working (In "Thisworkbook"):

Sub workbook_Open()
Sheet1.EnableAutoFilter = True
Sheet1.Protect userinterfaceonly:=True
Sheet2.EnableAutoFilter = True
Sheet2.Protect userinterfaceonly:=True
Protect Password:="pass"

End Sub


Even if I renamed the sheets I still have to use "Sheet1, Sheet2" etc
as commands


Phoenix :
 
Ad

Advertisements

D

Dave Peterson

When you used
sheet1.xxxx
You were using the codename for the worksheet. This codename is more difficult
for users to change, so it's probably a little safer.

Alternatively, you could have used:
worksheets("MySheet1").xxxx

When you're in the VBE next time, hit Ctrl-R (to see the project explorer).

Expand it to see the worksheets. You'll see something like:

Sheet1 (mySheet1)

The name to the left is the codename. The name in parentheses is the worksheet
name that appears on the worksheet tab that the users can see.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top