Can an Excel template be opened with all passwords bypassed?

This SHOULD not be a problem.  For your template, do you have a different password for the sheet, workbook and VBA Project?  Do you need to be prompted for each, or can the same password be used for all and you just want an UNLOCK and LOCK routine?

Cheers,

Dave

Here I believe is your solution.

on workbook open, you'll be asked if you want to unlock everything (which individually calls routines to unlock sheets, unprotect workbook, and unlock VBA code - leveraging 3 constants that are defined for passwords on each).

There's also a lockEverything() routine that uses the same passwords to lock everything down.

I think you can take it from here- determining whether you want to prompt for one password to use for all, or changing the CONST definitions for each of the 3 passwords, etc.

However, don't hesitate to ask if any questions:

here's the code - on workbook open:

 

Private Sub Workbook_Open()
Dim xMsg As Integer

    xMsg = MsgBox("Unprotect Everything?", vbYesNo, "Hit Yes to unprotect Workbook, Sheets, and VBA Code")
    
    If xMsg = vbYes Then
        Call unProtectWorkbook
        Call UnlockAllsheets
        Call UnlockVBAProject
    End If
End Sub

Select allOpen in new window

Here are the locking routines, with corresponding unlock routines immediately following:
 

Sub LockEverything()
    Call LockAllSheets
    Call ProtectWorkbook
    Call LockVBAProject
End Sub
Sub ProtectWorkbook()
    ThisWorkbook.Protect Password:=WkbPasswd, Structure:=True, Windows:=False   'you can set all the settings you want to restrict, here
End Sub
Sub unProtectWorkbook()
    ThisWorkbook.Unprotect Password:=WkbPasswd
End Sub
Sub LockAllSheets()
Dim WS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
        Call LockDownSettings(WS)
    Next WS
    
End Sub
Sub UnlockAllsheets()
Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        Call UnlockSettings(WS)
    Next WS
End Sub
Sub LockDownSettings(mySheet As Worksheet)
    
    With mySheet.Cells
        .Locked = False
        .FormulaHidden = False
    End With
    With mySheet
        .Protect Password:=shtPasswd, DrawingObjects:=True, Contents:=True, Scenarios:=True
        .EnableSelection = xlNoSelection 'if the sheet is unhidden, no cells can be selected
    End With

End Sub
Sub UnlockSettings(mySheet As Worksheet)
    
    mySheet.Unprotect Password:=shtPasswd

End Sub
Sub LockVBAProject()
    Call SetVBProjectPassword(ThisWorkbook, vbProjPassword)
End Sub
Sub UnlockVBAProject()
    Call UnlockTargetVBProject(ThisWorkbook)
End Sub

Select allOpen in new window

And here is the VBA Project Lock and Unlock detail (obtained courtesy:http://www.excelforum.com/excel-programming/762424-sendkeys-unlocks-vba-project-but-still-pops-up-password-prompt.html and http://www.mrexcel.com/archive/VBA/1100.html re: Bill Manville I believe originally developed this in 2000).

 

Public Sub UnlockTargetVBProject(aWB As Workbook)
'source: adapted from http://www.excelforum.com/excel-programming/762424-sendkeys-unlocks-vba-project-but-still-pops-up-password-prompt.html
'This procedure will unprotect an already opened target workbook passed in as the argument
'It will call the UnprotectVBProject() procedure immediately below

Dim wbName As Variant
Dim vbaProj As Object
Dim oWin As Object
Dim X As Integer

On Error GoTo ErrorHandler

'Assign an object ref to the target's VBA project
Set vbaProj = aWB.VBProject
wbName = aWB.Name

'Close any open code windows
For Each oWin In vbaProj.VBE.Windows
If InStr(oWin.Caption, "(") > 0 Then oWin.Close
Next oWin

'Application.VBE.MainWindow.Visible = False

'Check to see if the VBA project is already unlocked
If vbaProj.Protection <> 1 Then
    MsgBox "Target file's VBA Project is already unlocked.", 0
    Exit Sub
ElseIf vbaProj.Protection = 1 Then
    'We found the project to be locked
    On Error Resume Next
    Do While X < 4
        If vbaProj.Protection <> 1 Then
            'MsgBox "The VBA project for " & wbName & " was unprotected successfully. Programming update will start now.", 64
            'MsgBox "when done unlocking, X is: " & X
            Exit Do
        End If
        'By duplicating the lines below, I'm able to reduce the number of times this Do loop is run.
        UnprotectVBProject aWB, vbProjPassword
        UnprotectVBProject aWB, vbProjPassword
        X = X + 1
    Loop
    On Error GoTo 0
End If

ErrorExit:
Set vbaProj = Nothing
Exit Sub

ErrorHandler:
Select Case Err.Number
    Case 1004
        MsgBox "You will need to set the " & _
            "{ TRUST ACCESS TO VISUAL BASIC PROJECT } setting" & vbNewLine & _
            "When the dialog appears, go to the Trusted Sources tab, " & _
            "check the setting, click OK, and re-start the update on a new copy of the old workbook again.", 64
        SendKeys "%T", True
        SendKeys "M", True
        SendKeys "S", True
    Case Else
        MsgBox Err.Description
End Select

Resume ErrorExit

End Sub
Public Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
'source: adapted from http://www.mrexcel.com/archive/VBA/1100.html
Dim vbProj As Object

On Error GoTo ErrorHandler

Application.ScreenUpdating = False
Set vbProj = WB.VBProject

'Check to see if VBA project is already unlocked
If vbProj.Protection <> 1 Then Exit Sub

'Activate chosen VBA Project
Set Application.VBE.ActiveVBProject = vbProj

'SendKeys is the only way
Application.SendKeys Password & "~~{ESC}"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

'Not the right password
If vbProj.Protection = 1 Then
    SendKeys "%{F11}", True
End If

' Clean up
Password = ""
Application.ScreenUpdating = True
Set vbProj = Nothing

Exit Sub

ErrorHandler:
MsgBox Err.Description, 64
End Sub

Sub SetVBProjectPassword(WB As Workbook, ByVal Password As String)
' used during making of new protected app, if user changes password
Dim VBP As Object, oWin As Object
Dim wbActive As Workbook
Dim i As Integer

Set VBP = WB.VBProject
Set wbActive = ActiveWorkbook

'can't do it if locked!
If VBP.Protection = vbext_pp_locked Then Exit Sub

Application.ScreenUpdating = False

' close any code windows to ensure we hit the right project
For Each oWin In VBP.VBE.Windows
If InStr(oWin.Caption, "(") > 0 Then oWin.Close
Next oWin

WB.Activate
' now use lovely SendKeys to set the project password
Application.OnKey "%{F11}"
SendKeys "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & _
Password & "~%{F11}", True

' leave no evidence of the password
Password = ""
' go back to the previously active workbook
wbActive.Activate
End Sub

Select allOpen in new window

See attached workbook where this all works - and remember your passwords!

Cheers,

Dave
LockAndUnlockWorkbook-r2.xls

Final note - of course, any password can be broken :)

Dave

thank you dlmille --- something important blew up here, but I'll test this as soon as I fix this other mess....sorry for delay.

Dlmille - finally got my head above water and I'm ready to try this, but before I do.....I don't want all users to be prompted when they open a copy of the template through a double-click. Is that what this code will do?

Instead, I only want sheet, workbook, and code passwords to automatically unlock everything IF the template is opened for editing (right-click, open, provide secret password, which I know is not foolproof).

Thanks.....very sorry for delay here.

This fires the protection macro anytime the file is opened.  At the start, it prompts the user whether to unlock the worksheet or not.  You need to save it in template form, when you're ready.

Would you like a button or menu item that they can select e.g., "open for editing"?  Otherwise, I'm not sure I understand your question.

Please try it and look at the code, then come back and help me to clarify my understanding of your need.

Cheers,

Dave

dlmille: I think this is not what I wanted. I already have a template with many sheets. Sheets have different passwords.

I do NOT want this to prompt the user when they double-click my template to use it. I would like the passwords to be "unlocked" when **I** am opening the template for editing, using the Modify password that I set at the file leve.

Give me a few - I will modify the workbook_open event to test whether the file is opened in read/write and then it will automatically unlock everything based on embedded passwords.

Ok?

Dave

Wow. Sounds great. Just to be sure, I'll rephrase what I need:
Template file
 (1) when user double-clicks the template to create an INSTANCE, no prompting and all passwords that are already set are in place.
 (2) when I right-click template, choose Open, and enter my password to Modify the actual .XLT file, I'd love to have the passwords bypassed.

Test this for now, then we'll talk about templates, which is an additional step.

The password for filemodify is "Password".  The password for all sheets and VBA Project is "Password".  You'll have to create a static array of passwords if you're going to have a different password on each sheet.

First, let's see if the BEHAVIOR is working correctly.

First open without filemodify and test.  Then, with filemodify and test.

Let me know

Dave

Here's the file to test :)

Dave
LockAndUnlockWorkbook-r2.xls

Ok - now, its a template.

I added this code, which checks for readonly to do its work, also it locks the template back down before saving, if you're editing...  I've also made the subroutines "PRIVATE" so they can't be called from the Developer's Ribbon.  I had to use the Application.Run command then to execute the routines, at the appropriate times.

Let me know if you need further assistance.

Dave

Private Sub Workbook_Open()

    If Not ActiveWorkbook.ReadOnly Then
        Application.Run "unLockEverything"
    End If

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    If Not ActiveWorkbook.ReadOnly Then
        Application.Run "LockEverything"
    End If

End Sub

Select allOpen in new window

Here's the file...

Dave
LockAndUnlockWorkbook-r3.xlt

Dave, I'm not sure I understand.... when I double-click your template pretending I'm a user where everything should be protected, and I'm prompted for a "VBA Project Password." I shouldn't be offered this choice here.  Then, when I right-click your template and choose Open to pretend I need to modify the original template, I am prompted for the modify password, and then again I'm prompted for a "VBA Project Password." So pretty much the same thing is happening in both instances.

It should not be.  Tell me how you are accessing the template (baby steps, please).  I don't get your double click and right click expressions.  Perhaps we access them differently.

In the meantime, I'll test my template again as it behaves as advertised on my desktop.

Dave

If a template file is sitting on my desktop, I double-click it to open an instance (copy) of the template to use. When saved, this will save as a regular XLS file. This is the same concept as using the normal template and seeing Book1......if my template is named Document, double-clicking the template will open an instance (copy) of the file and it will show as Document1.

If that same template file on my desktop is right-clicked, and "Open" is selected from the menu, the actual template itself is being opened, which allows me to edit the actual template itself because it opens the actual XLT file for modification.

Does that help?

Ok the template is a bit of a strange animal.

Would you kindly test this to see if the behavior is right, while I school a bit more on the template?

Much appreciated.

Dave
LockAndUnlockWorkbook-r3.xls

Would you kindly send me a template (no confidential data) where you've password protected everything (and share that password) that I can play with for this example?  I'd like to see what you're seeing with the template.

When I save the .xls as a template, it works properly on the rt-click open (see attached), but when I double-click, it creates a new instance of the template in the form of an XLS, so the fileModify is set to TRUE and my logic falls off the map :).  I'm going to fix this, but wanted to see if you're seeing what I'm seeing and if you can provide an example as well.

Sorry about this - please be patient - the lock/unlock stuff is easy, just trying to work this now into your template....

if you have Skype or Live and want to chat it through, I can do that, and the results will still be posted here for the knowledgebase.

Dave
LockAndUnlockWorkbook-r4.xlt

I think I've got it.

Please test this - opening in the various ways, and advise if it works.  Because, it works for me on double-click, or right-click, etc...

Then, I can share with you more on how to adapt it further to your needs.

Dave
LockAndUnlockWorkbook-r4.xlt

You're great, Dave! Attached is a sample template file. Drag it to your desktop and then:
(1) if you RIGHT-click and choose open, enter the password "mine" (without the quotation marks) to open the template for editing. Here is where I'd love to have all passwords already clicked off.

This file contains:
     Sheet named SW Worksheet, sheet password = special
     Sheet named Equipment Worksheet, sheet password = user
     hidden sheet named Checklist, sheet password = user
     hidden sheet named PF-5Line, sheet password = mine
     WorkBOOK password = book
     VBA Code password = code

(2) DOUBLE-click to get a copy of the template, where all passwords SHOULD be in place and protecting stuff. Here, you should only see two sheets: SW Worksheet and Equipment Worksheet, and they both should be password protected along with a workbook password.

Hope this helps....Thanks so much! Template-password-bypass.xlt

BTW, Skype and all manner of chats are blocked by the sysadmin where I work. Sorry....Nancy

Cool.  I'll put this methodology into your example template to ensure it works.  

PS - did the template on my last post work as you desire?

Will post after lunch...

Dave

Sorry, Dave -- didn't see your post in between (ID: 35192182). This sample seems to work beautifully. My only questions are: (1) when I right-click to open/modify the template itself, and I supply the modify password, am I supposed to be prompted for a VBA password? And if so, (2) why after supplying that password does it leave the VBA Project Properties dialog box open?

Cool.....!!!

You are not supposed to be prompted at all (re: my last post of the template)

Be sure you don't move the mouse and keep your hands off the keyboard after responding to the fileModify prompt - as the VBA unlock password code uses SEND KEYS.

Try that and let me know.  I might have to put some code in to keep any keys/mouse clicks from interfering with the macro, lol.

Dave

Yes, after entering "Password" to open/modify the template from a right-click, I'm prompted with an inputbox saying "VBA Project Password", and the word Password above the text box.

This happens if I press Enter after entering the open/modify password, and also if I use the mouse to click the OK button after entering the open/modify password.

ok - will perhaps need to "pause" a bit more in the macro.  You're running Excel 2003?

PS - if you have a different sheet password on every sheet (potentially), then perhaps having a master sheet of passwords in the template (that no one but you can see) would be appropriate?

Dave

Ok - there's a bit of a timing issue with the SendKeys.

Would it be acceptable for you to have Excel Open, then minimize, and do your rt-click open?  Does it make it through all the password hoopla for you in this type of scenario?

Dave

I'm using Excel 2007, but the file formats are all Excel 2003 for backward compatibility of some users.

I followed  your steps in ID 35193710, and no difference. Still saw the inputbox after giving Excel the file-level password needed to open/modify.

A hidden (I would do xlVeryHidden) sheet of passwords would be fine.

I sure do appreciate your help!!! ~~Nancy

Ok - I'm working on all that setup.  When I get that working, we can come back to this pesky problem with the inputbox...

Dave

Ok - we're there or almost there.  Need to focus on the pesky VBA project thing - let me know if you're still getting the input box.

IF the VBA project remains locked after you open for modify.  Try the macro button on the HoldPasswords tab and let me know if ThAT works...

Dave

So, now please check this out, review the documentation in the sheet called "HoldPasswords" and play with it.  Let me know if you have any other issues.

This application is a template which has a sheet called "HoldPasswords" that hold the:
The application on open in read-only mode, or as a template for use by users, just opens itself with no macros associated with protection being invoked.  All modules are private (thus the Application.Run commands in the vba code to invoke them) so the users cannot see them from the Developer's ribbon.

There's a routine called getpasswords - that retrieves all stored passwords on the sheet. There are two driving routines:

UnlockAsSpecified() and LockAsSpecified() with very similar logic:

This last, unlocking the VBA Project is a result of executing SendKeys commands.  The application has to ensure the active workbook is in focus before implementation, and the user cannot be clicking or typing on the keyboard during this brief activity.

PROCESS STEPS as documented in the HoldPasswords sheet tab:

            Instructions for deployment:      
                  
Big Step 1      1.  Create a new template (or obtain an existing template), and ensure all settings are locked/unlocked per your preference, but have no passwords set, as this will come later
            So, go into each and PROTECT them, setting all the settings, but no password.  

            2.  Delete all tabs in this template, except for "HoldPasswords"      
            3.  Move the tabs from your template (re: Step 1) into this template      
            4.  Save the file as a template, then SAVE AGAIN (re: template file name has changed, so the RED cell, above, needs to have calculated and be saved with the template)      
                  
Big Step 2                  
            1.  On this tab, hit the "Generate list of sheets" button, to create the list of sheets with the yellow-shaded password cells to the right      
            2.  Enter the passwords for THIS sheet, the workbook template, and the VBA Project, above, in the grey-shaded cells      
            3.  When you're ready, just save the template - a macro will kickoff to set all the passwords and lock everything down      
                  
Test            Open in double-click mode - are things locked that should be locked?      
            Open in right-click mode, entering FileModify password - does everything unlock?      
                  
Celebrate            Hopefully, all works, and now you have a time-saving step!      
-----------------
Cheers,
                  
Dave

Type shtPasswd
    sht As Worksheet
    sht_Passwd As String
End Type
Private shtPwd() As shtPasswd
Const allShtPwd = "Password" 'for when all sheets have the same password, the password is saved here
Private WkbPassword As String
Private vbProjPassword As String
Private holdShtPassword As String
Private Sub getPasswords()
Dim inCursor As Range, i As Integer

    vbProjPassword = ThisWorkbook.Sheets("HoldPasswords").Range("VBAPwd").Value
    WkbPassword = ThisWorkbook.Sheets("HoldPasswords").Range("WkbPwd").Value
    holdShtPassword = ThisWorkbook.Sheets("HoldPasswords").Range("HoldShtPwd").Value
    
    Set inCursor = ThisWorkbook.Sheets("HoldPasswords").Range("shtStart")
    
    Erase shtPwd()
    i = 0
    For Each mycell In Range(inCursor, inCursor.End(xlDown))
        ReDim Preserve shtPwd(i) As shtPasswd
        Set shtPwd(i).sht = ThisWorkbook.Worksheets(mycell.Value)
        shtPwd(i).sht_Passwd = mycell.Offset(0, 1).Value
        i = i + 1
    Next mycell
    
End Sub
Private Sub LockAsSpecified()
'uses the information on the hidden sheet "HoldPasswords" to obtain specific sheet/password information
Dim i As Integer

    Call getPasswords
    
    'lock sheets first
    For i = 0 To UBound(shtPwd)
        Call LockDownSettings(shtPwd(i).sht, shtPwd(i).sht_Passwd)
    Next i
           
    'hide the HoldPasswords sheet
    ThisWorkbook.Sheets("HoldPasswords").Visible = True
    ThisWorkbook.Sheets("HoldPasswords").Visible = xlSheetVeryHidden
    
    'lock the HoldPasswords sheet
    Call LockDownSettings(ThisWorkbook.Sheets("HoldPasswords"), holdShtPassword)
    
    'lock workbook template next
    Call ProtectWorkbook(WkbPassword)
    
    'now lock the VBA Project (this app does not set VBA project passwords - it just checks the LOCKED switch)
    Call LockVBAProject
    
End Sub
Private Sub unLockAsSpecified()
'uses the information on the hidden sheet "HoldPasswords" to obtain specific sheet/password information
Dim i As Integer

    Call getPasswords
    
    'lock sheets first
    For i = 0 To UBound(shtPwd)
        Call UnlockSettings(shtPwd(i).sht, shtPwd(i).sht_Passwd)
    Next i
        
    'lock workbook template next
    Call unProtectWorkbook(WkbPassword)
    
    'lock the HoldPasswords sheet
    Call UnlockSettings(ThisWorkbook.Sheets("HoldPasswords"), holdShtPassword)
    
    'unhide the HoldPasswords sheet
    ThisWorkbook.Sheets("HoldPasswords").Visible = True
    
    'now lock the VBA Project (this app does not set VBA project passwords - it just checks the LOCKED switch)
    Call UnlockVBAProject
    
End Sub
Private Sub LockEverything()
    Call LockAllSheets
    Call ProtectWorkbook
    Call LockVBAProject
End Sub
Private Sub unLockEverything()
    Call unProtectWorkbook
    Call UnlockAllsheets
    Call UnlockVBAProject
End Sub
Private Sub ProtectWorkbook(Optional pwd As Variant)
'if the optional pwd exists, then there could be different passwords on different sheets
Dim myPwd As String

    If IsMissing(pwd) Then
        myPwd = allShtPwd
    Else
        myPwd = pwd
    End If
    
    ThisWorkbook.Protect Password:=myPwd, Structure:=True, Windows:=False   'you can set all the settings you want to restrict, here, or set them once at the user interface
    
End Sub
Private Sub unProtectWorkbook(Optional pwd As Variant)
'if the optional pwd exists, then there could be different passwords on different sheets
Dim myPwd As String

    If IsMissing(pwd) Then
        myPwd = allShtPwd
    Else
        myPwd = pwd
    End If
    
    ThisWorkbook.Unprotect Password:=myPwd
End Sub
Private Sub LockAllSheets()
Dim WS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
        Call LockDownSettings(WS)
    Next WS
    
End Sub
Private Sub UnlockAllsheets()
Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        Call UnlockSettings(WS)
    Next WS
End Sub
Private Sub LockDownSettings(mySheet As Worksheet, Optional pwd As Variant)
'if the optional pwd exists, then there could be different passwords on different sheets
Dim myPwd As String

    If IsMissing(pwd) Then
        myPwd = allShtPwd
    Else
        myPwd = pwd
    End If
    
    On Error Resume Next 'just in case there is no password
    mySheet.Unprotect Password:=myPwd
    'With mySheet.Cells
    '    .Locked = False
    '    .FormulaHidden = False
    'End With
    On Error GoTo 0
    
    'ok, so now the sheet is unprotected, so it should protect correctly, with the right password
    With mySheet
        .Protect Password:=myPwd, DrawingObjects:=True, Contents:=True, Scenarios:=True
        '.EnableSelection = xlNoSelection 'if the sheet is unhidden, no cells can be selected - commented out as this is user definable, on a sheet by sheet basis
    End With

End Sub
Private Sub UnlockSettings(mySheet As Worksheet, Optional pwd As Variant)
'if the optional pwd exists, then there could be different passwords on different sheets
Dim myPwd As String
    
    If IsMissing(pwd) Then
        myPwd = allShtPwd
    Else
        myPwd = pwd
    End If
    
    mySheet.Unprotect Password:=myPwd

End Sub
Private Sub LockVBAProject()
    Call SetVBProjectPassword(ThisWorkbook, vbProjPassword)
End Sub
Private Sub UnlockVBAProject()
    Call UnlockTargetVBProject(ThisWorkbook)
End Sub
Private Sub UnlockTargetVBProject(aWB As Workbook)
'source: adapted from http://www.excelforum.com/excel-programming/762424-sendkeys-unlocks-vba-project-but-still-pops-up-password-prompt.html
'This procedure will unprotect an already opened target workbook passed in as the argument
'It will call the UnprotectVBProject() procedure immediately below

Dim wbName As Variant
Dim vbaProj As Object
Dim oWin As Object
Dim X As Integer

On Error GoTo ErrorHandler

'Assign an object ref to the target's VBA project
Set vbaProj = aWB.VBProject
wbName = aWB.Name

'Close any open code windows
For Each oWin In vbaProj.VBE.Windows
If InStr(oWin.Caption, "(") > 0 Then oWin.Close
Next oWin

'Application.VBE.MainWindow.Visible = False

'Check to see if the VBA project is already unlocked
If vbaProj.Protection <> 1 Then
    'MsgBox "Target file's VBA Project is already unlocked.", 0
    Exit Sub
ElseIf vbaProj.Protection = 1 Then
    'We found the project to be locked
    On Error Resume Next
    Do While X < 4
        If vbaProj.Protection <> 1 Then
            'MsgBox "The VBA project for " & wbName & " was unprotected successfully. Programming update will start now.", 64
            'MsgBox "when done unlocking, X is: " & X
            Exit Do
        End If
        'By duplicating the lines below, I'm able to reduce the number of times this Do loop is run.
        UnprotectVBProject aWB, vbProjPassword
        Application.Wait Now + TimeValue("00:00:01") 'added by dlmille of E-E
        UnprotectVBProject aWB, vbProjPassword
        Application.Wait Now + TimeValue("00:00:01") 'added by dlmille of E-E
        X = X + 1
    Loop
    On Error GoTo 0
End If

ErrorExit:
Set vbaProj = Nothing
Exit Sub

ErrorHandler:
Select Case Err.Number
    Case 1004
        MsgBox "You will need to set the " & _
            "{ TRUST ACCESS TO VISUAL BASIC PROJECT } setting" & vbNewLine & _
            "When the dialog appears, go to the Trusted Sources tab, " & _
            "check the setting, click OK, and re-start the update on a new copy of the old workbook again.", 64
        SendKeys "%T", True
        SendKeys "M", True
        SendKeys "S", True
    Case Else
        MsgBox Err.Description
End Select

Resume ErrorExit

End Sub
Private Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
'source: adapted from http://www.excelforum.com/excel-programming/762424-sendkeys-unlocks-vba-project-but-still-pops-up-password-prompt.html
Dim vbProj As Object

On Error GoTo ErrorHandler

Application.ScreenUpdating = False
Set vbProj = WB.VBProject

'Check to see if VBA project is already unlocked
If vbProj.Protection <> 1 Then Exit Sub

'Activate chosen VBA Project
Set Application.VBE.ActiveVBProject = vbProj

'SendKeys is the only way
Application.SendKeys Password & "~~{ESC}"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

'Not the right password
If vbProj.Protection = 1 Then
    SendKeys "%{F11}", True
End If

' Clean up
Password = ""
Application.ScreenUpdating = True
Set vbProj = Nothing

Exit Sub

ErrorHandler:
MsgBox Err.Description, 64
End Sub

Private Sub SetVBProjectPassword(WB As Workbook, ByVal Password As String)
' used during making of new protected app, if user changes password
Dim VBP As Object, oWin As Object
Dim wbActive As Workbook
Dim i As Integer

Set VBP = WB.VBProject
Set wbActive = ActiveWorkbook

'can't do it if locked!
If VBP.Protection = vbext_pp_locked Then Exit Sub

Application.ScreenUpdating = False

' close any code windows to ensure we hit the right project
For Each oWin In VBP.VBE.Windows
If InStr(oWin.Caption, "(") > 0 Then oWin.Close
Next oWin

WB.Activate
' now use lovely SendKeys to set the project password
Application.OnKey "%{F11}"
'SendKeys "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & _
'Password & "~%{F11}", True
SendKeys "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password, True
SendKeys "%{F11} ' revert back to sheet"
' leave no evidence of the password
Password = ""
' go back to the previously active workbook
wbActive.Activate
End Sub

Select allOpen in new window

LockAndUnlockWorkbook-r5.xlt

Nancy - were you able to look over this, today?

Dave

PS - I've just managed to test on Excel 2003 instance, and my Excel 2007 Instance at home - and I'm also having a colleague test.

Feel free to describe precisely what happens (perhaps with screen print image posted after?) if there are problems with the unlocking of the VBA Project still.

Cheers,

Dave

Hi, Dave: Finally got some time to study this. Wow!

Question - can I take your code and the HoldPasswords sheet into my template instead of putting my template sheets and code (lots of both) into your file? Seems like much less copying if that will work.

This is a beautiful high-powered animal you created, and I'm learning a LOT from it.

Comment -- I'm still getting prompted for the VBA password when I open your template with the modify password. Here are screen shots of the 3 steps before the correctly unlocked file is available. step-2-at-open-modify.jpg
step-3-at-open-modify.jpg

I see that And I've tried to duplicate it - on both Excel 2007 and 2003.  Also, I sent to a colleague who had no problems, either.  Folks have been using the code for unlocking the VBA project for years.. Trying to sort this with you....

Try having Excel open, then minimized, then try opening with FileModify.  Also, try this on ANOTHER machine and see if it makes a difference.

.................

In the meantime, I think you SHOULD be able to move the password file and code models (Module:Protection handling, and ThisWorkbook)  over to a new template.

Let me know if that works...

Dave

PS - ignore the unlock button on the HoldPasswords tab for now.  It should be unlocking from the very beginning, when you put in the FileModify password.

Dave

Dave, I'm attempting to move your template goodies into a copy of my template file...Question: I have code for Workbook_Open that I'll need to comment out, because it would conflict with your Workbook_Open code. Question is this: my code sets EnableOutlining = True, which I need on a few sheets....how do I get that using your code?

Dave, I've incorporated your goodies into my template file, and now that I'm finished, when I try to save my template (Big Step 2, #3), i get "Run-time error 1004: The password you supplied is not correct. Verify that the CAPS LOCK key......" and when I click the Debug button, it takes me to UnlockSettings(), this line:    mySheet.Unprotect Password:=myPwd

send me one of your templates and I'll incorporate and document the steps I went through to get there.

Ok?

Dave

Otherwise, try to set all your sheet passwords and workbook password and VBA project password in accordance with the HoldPasswords tab - BEFORE trying to save.  That's the problem.

Cheers,

dave

Just do all your Workbook_open code after my workbook_open code...

Again - send me a test template and I'll convert and document what I did to get there, so it will be repeatable for you.

Dave

Hi Dave - I really appreciate your diligence here!
Question: does your code allow me to also protect the workbook for Structure = True? I don't find that anywhere on the HoldPasswords tab.
Also, how do I send my file to you? It contains confidential info, so I'd rather not post here.

Dave:
<< Otherwise, try to set all your sheet passwords and workbook password and VBA project password in accordance with the HoldPasswords tab - BEFORE trying to save.  That's the problem.>>

When I open the file to modify and the code bombs out, I can't unlock any of the sheets with their passwords.....(or any other passwords). Same is true if I open a copy of the template like a user would do.....can't unlock any sheets.

Ok.  Let's do this from scratch.

I started with my macro template and deleted all tabs, except for HoldPasswords

I then made modifications to the code to allow you to LOCK or UNLOCK with a flag in the HOLD PASSWORDS tab (for debugging purposes, we'll set that to FALSE as we build our new template - that way, we can save, etc.)

I also noticed you like to hide sheets, so I added that field in the HoldPasswords tab, so when you lock and save, those sheets will be hidden, as well.

I then MANUALLY changed the workbook template and HoldPasswords password to NOTHING by ensuring nothing was protected - need to do this, as the macro uses the "new" password in the HoldPasswords tab to set the workbook template and HoldPasswords tab, as well as the VBA Project.

I saved the template (see attached).

Then, I followed the instructions to modify a template, using this "add-on"...

1.  I started with the template you originally gave me, re: Template-password-bypass.xlt

2.  After opening it, I unprotected everything (note your settings are still there, so if you want STRUCTURE or other things set, set them, but put no password - when password is applied, all those settings should be set, as well)

3.  I then unhid hidden worksheets - recall, need to get everything without passwords as a starting point.
4.  I then opened the file, LockandUnlockWorkbook r6.xlt (password= "Password") and moved the ThisWorkbook and ProtectionHandling code to the new/existing template, followed by moving the tab with the HoldPasswords to the template.  At this point, there's now only one open file - the template-password-bypass.xlt file.
5.  From the HoldPasswords tab, I hit the Generate List of Sheets button


6.  I put in the passwords for each sheet

7.  I marked each sheet as being visible, hidden, or very hidden, according to the documentation you provided, setting PF-5Line to xlSheetHidden, Checklist to xlSheetHidden, and AddedCosts to xlSheetVeryHidden.

8.  I entered the password "book" for the workbook template, and "Password" for the HoldPasswords tab and VBA Password

9. I saved as template-password-bypass r1.xlt file, and saved again (to ensure the F2 range of HoldPasswords tab updated with the latest file name!)

10.  Then, I changed the LOCK flag on the HoldPasswords tab (cell F8) to TRUE and saved again.  Then, I changed the VBA Project to LOCKED with the password="Password" (important step)
 11.  I closed the template, then opened with the FileModify Password "mine" for your template.
 12.  It opened and displayed the two unhidden worksheets, and HoldPassword, unlocking everything successfully.

13.  I printed these instructions and followed them again to build the template, successfully.

14.  I suggest you start with the template, template-bypass-password.xlt, using the macro template BLANK, LockAndUnlockWorkbook-r6.xlt and try to generate the new template, template-bypass-password r1.xlt (see attached files).  Please advise how it goes.

Note - the VBA Project Unlock code is executed (for now, anyway - given the problems we've had on your end - as it works on mine and others, for some reason!) in the HoldPasswords Tab at the click of a button.  Let's see if THAT works :)

Cheers,

Dave  
Template-password-bypass.xlt
LockAndUnlockWorkbook-r6.xlt
Template-password-bypass-r1.xlt

Dave, I'm in the process of following your latest steps with my real file, and I have these question:
Step 2: I want to verify that I opened the template itself, not a copy of it. Correct?
Step 2: Do I also need to remove the template's open/modify password at this step?
Step 2: When I remove the password from my VBA code, VBA doesn't allow me to keep the checkmark in the box labeled "Lock project for viewing," so how will this get set later?
Step 2: I need some sheets to have "EnableOutlining = True" and the only way I know to set that option is in code. See my next question....
Step 4: I pasted your Workbook_Open code above my own, but I'm afraid that my own code here will mess things up...see code attached.
Step 4: Ditto with your Workbook_BeforeSave code....I put it before my own, but I'm afraid that my own code might fight with it.....see code attached.

Meanwhile, I'll continue the process. Thanks SO much!

Private Sub Workbook_Open()
    'added from EE - dlmille
    If Not ThisWorkbook.ReadOnly And ThisWorkbook.name = ThisWorkbook.Sheets("HoldPasswords").Range("thisTemplateName").Value _
        And ThisWorkbook.Sheets("HoldPasswords").Range("templateLocked").Value Then
            Application.Run "unLockAsSpecified"
    End If
    'EO code from EE

    'added 7/28/2010
    Const pwSW = "KagAcr8z"
    Const pwMine = "Rk8bT95"
    Const pwEq = "syzygy"
    Const pwBook = "3253"
    Application.ScreenUpdating = False
    With Worksheets("SW Worksheet")
        .Protect Password:=pwSW, AllowFormattingCells:=True, UserInterfaceOnly:=True
        .EnableOutlining = True
    End With
    With Worksheets("Equipment Worksheet")
        .Protect Password:=pwEq, AllowFormattingCells:=True, UserInterfaceOnly:=True, _
        AllowFiltering:=True
        .EnableOutlining = True
    End With
        With Worksheets("PF Cost Summary")
        .Protect Password:=pwMine, AllowFormattingCells:=True, UserInterfaceOnly:=True
        .EnableOutlining = True
    End With
    With Worksheets("PF-5Line")
        .Protect Password:=pwMine
    End With

    ActiveWorkbook.Protect Password:=pwBook, structure:=True
    Application.ScreenUpdating = True

End Sub

Select allOpen in new window

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'code from EE - dlmille
    If Not ThisWorkbook.ReadOnly And ThisWorkbook.name = ThisWorkbook.Sheets("HoldPasswords").Range("thisTemplateName").Value _
        And ThisWorkbook.Sheets("HoldPasswords").Range("templateLocked").Value Then
        
            Application.Run "unLockAsSpecified"
            Application.Run "LockAsSpecified"
    End If
    'EO EE code
    
    Const pwBook = "3253"
    Dim FileFormatValue As Long
    Dim rng As Range
    Dim l As Range
    Dim store As String
    Dim ver As String
    Dim fName As String
    Dim theirPath As String
    Dim needPath As Integer

    If UCase$(Right$(ThisWorkbook.name, 3)) = "XLT" Then
        'get out because it's me updating the original template file
        Exit Sub
    End If
    
    'make sure SW Worksheet F27:F46 contains data
    Application.ScreenUpdating = False
    Worksheets("SW Worksheet").Activate
    Set rng = Worksheets("SW Worksheet").Range("F27:F46")
    For Each l In rng
        If IsEmpty(l.Value) Then
            MsgBox "Data is missing in cell F" & l.row & " on the SW Worksheet." _
                & vbCrLf & "Please be sure all cells from F27 through F46 have data."
            Worksheets("SW Worksheet").Range("F" & l.row).Activate
            Cancel = True
            Exit Sub
        End If
    Next l
    
    'refers to and automatically runs the InvalidSWTotal function
    If InvalidSWTotal = True Then
        Cancel = True
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    Application.DisplayAlerts = False
    If SaveAsUI = True Then     'it's a SaveAs
        ActiveWorkbook.Protect Password:=pwBook, structure:=True
        store = Worksheets("SW Worksheet").Range("H5").Value
        ver = Worksheets("SW Worksheet").Range("N1").Value
        fName = store & " SOW ver " & ver
        
        Application.ScreenUpdating = True
        theirPath = GetFolderName("Select a folder")
        If theirPath = "" Then
            Do Until theirPath <> ""
                needPath = MsgBox("You need to select a folder.", vbOKCancel)
                If needPath = vbCancel Then
                    Cancel = True
                    Exit Sub
                End If
                theirPath = GetFolderName("Select a folder")
            Loop
        End If

        Application.ScreenUpdating = False
        With Application
            .EnableEvents = False
            .DisplayAlerts = False
            .ScreenUpdating = False
            Me.SaveAs theirPath & "\" & fName & ".xls", FileFormat:=xlNormal, CreateBackup:=False
            .EnableEvents = True
            .DisplayAlerts = True
            .ScreenUpdating = True
        End With
        Cancel = True
    Else
        'it's Save, NOT SaveAs
        ActiveWorkbook.Protect Password:=pwBook, structure:=True
        ThisWorkbook.Saved = True
        Exit Sub
    End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Select allOpen in new window

Dave, disregard my question above "Step 2: When I remove my password from my VBA code, VBA doesn't allow me........", since I discovered the answer in Step 10.

Update: you can please disregard my FIRST THREE "Step 2" questions above. I completed the process with my real file. I believe that my Workbook_Open code is conflicting with yours because when I open/modify my new Souped-Up-Dave-template, everything is password protected.

So we (you!) are halfway there, because from the user perspective of opening a copy of the template, it was all correct. Really cool!

Please advise why, on Workbook_Open, you are executing password and structure related code?  Should that not be done with the workbook itself, and the tab HoldPasswords I created for you?  We can add the structure:=True on the workbook as part of the locking macro, no problem - or add it as an option in the HoldPasswords tab.

HOWEVER, if you manually go through the process of protecting each sheet, and workbook - but without a password, then those settings will remain.  If they're always going to be the same settings, or you'd like those settings on the HoldPasswords tab, we can add them.

In both the Open and Close events, my code should be the only code that does the locking and unlocking, protecting and unprotecting.

I suggest putting my code at the top of the Workbook_Open event code, and my code at the bottom of the Workbook_BeforeSave event code (I noted you have conditions where you might be cancelling the save, so this is important).

Dave

Dave, I had that code in my Workbook_Open event just as a precaution to ensure things were password-protected. If I get rid of my Workbook_Open code, then I guess I will need your code to EnableOutlining for just my SW Worksheet and Equipment Worksheet sheets.

Meanwhile, I will follow your suggestion and let you know how it works. THanks!

Dave, this is all twisted around. When I open a COPY of the template, sheets are not protected and the HoldPasswords sheet is visible. When I open/modify the template itself, it tells me I'm using the wrong password and I get the Debug button grayed out and the End button available.

Should I give up and award you the well-deserved points, assuming this file is too complex? Or  can you look at my Workbook_BeforeSave code and see if anything should be commented out?

Nancy - sorry, I must have missed the alerts on these posts...  Let me review and respond.

Dave

>>I will need your code to EnableOutlining for just my SW Worksheet and Equipment Worksheet sheets

We can add "EnableOutlining" as an option on the sheets.

>>Should I give up
We are pretty close here, I wouldn't give up on this, yet - unless you've had enough, lol :)
I'm committed...

I know it takes some time to build one of these, but if you'd please create the template add-on from scratch, following the process - I need to know if all that works.  Then I can focus on the next step - adding your code in.  Just need to backtrack a step - if you recall, then you don't need to re-create - just let me know.

I'll look at your Workbook_BeforeSave code now

Dave

Here's the EnableOutlining option incorporated, on a sheet basis.  note, there's the routine LockDown... where you can set "Standard" settings for all sheets.  The enableoutlining you wanted to vary by sheet, so I added that capability at HoldPasswords.

Let me know...
:)

Dave
LockAndUnlockWorkbook-r7.xlt

PS - to add some security/comfort, when we think we're there, we could add an email alert to you if someone other than approved ID's get the workbook open without protection in place.

However, that should not be necessary.  Once you've tested a generated template, the code should run exactly the same, every time - no need to "ensure the password protection and hiding of certain sheets" as you'll have verified before distribution, correct?

Cheers,

Dave

Dave, I have had some issues with users who have Excel 2003 (I have 2007 but save the file as 2003 and try to only use code understood by 2003), and I cannot duplicate the problems on a 2003 machine.

Almost done re-creating the beast with the new LockandUnlock.....will let you know.

Cool - should work on both 2007 and 2003, but let's see where you get.

PS if there are other lock parameters you need that are for "all" sheets in the list - that goes in the Lock routine.  If its individual, you'd need to add features on the HoldPasswords sheets, or have me add them in a new query (if it comes up in future - if there are some now, let me know and I'll add).

Cheers,

Dave

You CAN or CANNOT duplicate the problems on 2003???  re: your comment started with  >> I have had some issues with users who have Excel 2003...

Dave

canNOT duplicate their problems with my Excel 2003 machine. Very frustrating....but that was the old world. This new Dave World hasn't gone out into the wild yet!  ;)

ok - WHEN we get it all working on your 2003/2007 implementations, I'll work with you on testing with adhoc users.

Rebuilt it and commented-out my Workbook_BeforeSave and my Workbook_Open code so only your code is running in those procedures. But when I open an instance of the template, everything is visible (except the HoldPasswords sheet) and unprotected (except the workbook itself, and the VBA code).

Is there a way I can send you the file (since it's a bit too private to post the real thing here)?

dlmille at hotmail dot com

First - did you:
In edit mode - change the TRUE to FALSE for activating the protection stuff?

If you do that and hit SAVE, you'll see what you get when you open in non-edit mode (re: what's visible, what's not, etc.) but you have to close and open to fully test.

Dave

When I changed TRUE to FALSE and clicked save, everything is visible and unprotected.
Then I change FALSE to TRUE and save again, and everything is visible EXCEPT the HoldPasswords sheet, and everything is unprotected except the workBOOK itself.

I'll send it to you.....right now, it has no open/modify password.

A million thanks for your persistence .....you've earned the points a thousand times over!

ok - could be an enableevents disconnect.  If it is, I'll put some code in to try to rectify that.

I'm anxious to see what your findings are...and to learn from the end product.

Ok - my bad - I did a "fix" in the last submittal in case there was only one sheet (I was using .end(xldown) and that won't work right if there's only one sheet) - and then, I only tested with one sheet, lol!

here's the mod I'm testing now see the commented line after the Else:


if it works, I'll revert for more testing.  When we're done, we can paste a clean template back here for the knowledgebase.

Dave

Private Sub getPasswords()
Dim inCursor As Range, i As Integer
Dim lastRow As Long

    vbProjPassword = ThisWorkbook.Sheets("HoldPasswords").Range("VBAPwd").Value
    WkbPassword = ThisWorkbook.Sheets("HoldPasswords").Range("WkbPwd").Value
    holdShtPassword = ThisWorkbook.Sheets("HoldPasswords").Range("HoldShtPwd").Value
    
    Set inCursor = ThisWorkbook.Sheets("HoldPasswords").Range("shtStart")
    
    Erase shtPwd()
    i = 0
    If inCursor.Offset(1, 0).Value = "" Then
        lastRow = inCursor.row
    Else
        'lastRow = Range(inCursor, inCursor.End(xlDown)).row
        lastRow = inCursor.End(xlDown).row
    End If

Select allOpen in new window

Notice,  there's a couple more buttons on the HoldPasswords, and I put in a few more actions to ensure events are enabled in case theres a messup with error in the middle of the open for edit.  Also, I added a new code module (I have two monitors, but you can put debugger on one side of screen and XLS on the other even on one monitor) - see the ProtectionTesting module - you can click on the subroutine and hit F5 to see what happens when Lock or UnLock is executed to help debug in future, in case we haven't trapped "every" possible error.
 


When we're done - let's make sure we go through the code and see if at least at a high level you don't have any questions I can answer in E-E so this can be re-usable by others (including me :)

Let me know if the VBA Project Unlock button is working for you.  The utility DOES NOT set the VBA PROJECT PASSWORD, so take some care here, going forward (remember, we have to use SendKeys for this):

Dave

LockAndUnlockWorkbook-r8.xlt

I wish I could award more points -- fantastic solution!