Capturing an Open Workbook Password

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have developed a personal tracking spreadsheet that I
occasionally send out an up-dated version to those using
it. The spreadsheet will automatically up-date the data
into the new version and save it under the same name. The
user simply clicks a data transfer button and is given the
opportunity to open the existing spreadsheet and transfer
the data to the new version. The problem is that I don't
know how to capture the password to include that in the
new up-dated spreadsheet so that the user doesn't have to
re-enter the password during the first save after the up-
date. Is it possible to capture the password of the
opened spreadsheet and save the new version with the same
password? Any help would be appreciated. Thanks!
 
You could just ask the user the password and then use it to unprotect the
worksheet, then you could do your stuff, then reprotect the sheet.

But then you'd have to check to see if the password they passed to you was
correct (and if your code screwed up the password, you'd have some explanation
to do).

I think I'd just pop up the unprotect dialogs and let the user have more
control.

Option Explicit
Sub testme02()

Dim OkToContinue As Boolean
Dim Wks As Worksheet
Dim wksWasProtected As Boolean

Set Wks = ActiveWorkbook.Worksheets("Sheet1") 'whatever it is
Application.Goto Wks.Range("a1") 'activate that sheet

wksWasProtected = False
If Wks.ProtectContents _
Or Wks.ProtectDrawingObjects _
Or Wks.ProtectScenarios Then
wksWasProtected = True
On Error Resume Next
Application.Dialogs(xlDialogProtectDocument).Show
On Error GoTo 0
End If

If Wks.ProtectContents _
Or Wks.ProtectDrawingObjects _
Or Wks.ProtectScenarios Then
MsgBox "Please provide the correct password when prompted" & _
"when you try this again."
Exit Sub
End If

'it's unprotected so do your stuff.

If wksWasProtected Then
Application.Dialogs(xlDialogProtectDocument).Show
End If

End Sub
 
Back
Top