Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help re VBA for Worksheet Open Event
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help re VBA for Worksheet Open Event
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help re VBA for Worksheet Open Event
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help re VBA for Worksheet Open Event
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help re VBA for Worksheet Open Event
Thanks so much! That code solved the problem.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Event | Excel Programming | |||
user form-on open event? keydown event? | Excel Programming | |||
Event Procedures: Event on Worksheet to fire Event on another Worksheet | Excel Programming | |||
Open event | Excel Programming |