Using VBA to Protect sheet(s) with Autofilter

[Phoenix]

Hi!

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

Sub Autpen()
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 Autpen()
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: Autpen"

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

What can be the problem

 

[Nick HK]

Phoenix,
Sub Autpen() 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 Autpen()
| 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 Autpen()
| 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: Autpen"
|
| It works fine with only sheet1 protected, but not with two or more??
|
| What can be the problem?
|
|
| ------------------------------------------------
| [/url]
|
|

 

[Phoenix]

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

Phoeni

 

[Phoenix]

Anyone

 

[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 :

 

[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.