Need help re VBA for Worksheet Open Event

Just upgraded from Office 97 (Excel 97) to Office 2003 (Excel 2003). Now
getting "Unprotect Worksheet" prompt with "OK" or "CANCEL" after enabling
macros, for each password-protected worksheet in workbook developed before
upgrade. From my research, it is likely a problem with my Workbook_open
macro. NEED HELP!

Workbook has 5 worksheets. Users enter data in "unlocked" cells. Have
password protected sheets to protect other formulas/formatting. Because
users must be able to use auto-filter at top of worksheet (doesn't work when
worksheet is protected), I pieced together the macro below. Since upgrade,
after enabling macros, users must hit "cancel" for each protected worksheet.
Many people access the workbook numerous times per day, so this is not
functional.

I only know how to "cut & paste" re VBA, so have exhausted my VBA knowledge.
I'm hoping you can help me again!

Private Sub Workbook_Open()
Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With

Dim WS As Worksheet
For Each WS In Worksheets
WS.EnableAutoFilter = True
WS.Protect contents:=True, userInterfaceOnly:=True
Next WS
End Sub

Try specifying the password in your .protect line.

Excel/VBA got a little more stringent (in xl2002, IIRC).



LucyRB wrote:
>
> Just upgraded from Office 97 (Excel 97) to Office 2003 (Excel 2003). Now
> getting "Unprotect Worksheet" prompt with "OK" or "CANCEL" after enabling
> macros, for each password-protected worksheet in workbook developed before
> upgrade. From my research, it is likely a problem with my Workbook_open
> macro. NEED HELP!
>
> Workbook has 5 worksheets. Users enter data in "unlocked" cells. Have
> password protected sheets to protect other formulas/formatting. Because
> users must be able to use auto-filter at top of worksheet (doesn't work when
> worksheet is protected), I pieced together the macro below. Since upgrade,
> after enabling macros, users must hit "cancel" for each protected worksheet.
> Many people access the workbook numerous times per day, so this is not
> functional.
>
> I only know how to "cut & paste" re VBA, so have exhausted my VBA knowledge.
> I'm hoping you can help me again!
>
> Private Sub Workbook_Open()
> Application.ScreenUpdating = False
>
> With Application
> .Calculation = xlManual
> .MaxChange = 0.001
> .CalculateBeforeSave = False
> End With
>
> Dim WS As Worksheet
> For Each WS In Worksheets
> WS.EnableAutoFilter = True
> WS.Protect contents:=True, userInterfaceOnly:=True
> Next WS
> End Sub

--

Dave Peterson

> Try specifying the password in your .protect line.

Thanks for your reply, David. I'm not sure how to do this (ie. VBA code) or
if this would produce the desired result: for the"Unprotect Sheet" password
prompt (with OK or Cancel as only options) to *not* appear when the users
open the workbook containing password-protected worksheets.

I may be misunderstanding the effect of putting the password in the .protect
line, however.

If the passwords are all the same:

Private Sub Workbook_Open()
Application.ScreenUpdating = False

Dim WS As Worksheet
dim myPWD as string

myPwd = "hithere"

With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With

For Each WS In Worksheets
WS.Protect password:="hithere", _
contents:=True, userInterfaceOnly:=True
WS.EnableAutoFilter = True
Next WS
End Sub

LucyRB wrote:
>
> > Try specifying the password in your .protect line.
>
> Thanks for your reply, David. I'm not sure how to do this (ie. VBA code) or
> if this would produce the desired result: for the"Unprotect Sheet" password
> prompt (with OK or Cancel as only options) to *not* appear when the users
> open the workbook containing password-protected worksheets.
>
> I may be misunderstanding the effect of putting the password in the .protect
> line, however.

--

Dave Peterson

I meant this:
WS.Protect password:=myPWD, _
contents:=True, userInterfaceOnly:=True



Dave Peterson wrote:
>
> If the passwords are all the same:
>
> Private Sub Workbook_Open()
> Application.ScreenUpdating = False
>
> Dim WS As Worksheet
> dim myPWD as string
>
> myPwd = "hithere"
>
> With Application
> .Calculation = xlManual
> .MaxChange = 0.001
> .CalculateBeforeSave = False
> End With
>
> For Each WS In Worksheets
> WS.Protect password:="hithere", _
> contents:=True, userInterfaceOnly:=True
> WS.EnableAutoFilter = True
> Next WS
> End Sub
>
> LucyRB wrote:
> >
> > > Try specifying the password in your .protect line.
> >
> > Thanks for your reply, David. I'm not sure how to do this (ie. VBA code) or
> > if this would produce the desired result: for the"Unprotect Sheet" password
> > prompt (with OK or Cancel as only options) to *not* appear when the users
> > open the workbook containing password-protected worksheets.
> >
> > I may be misunderstanding the effect of putting the password in the .protect
> > line, however.
>
> --
>
> Dave Peterson

--

Dave Peterson

Thanks so much! That code solved the problem.