password to save

**The_Cat** · 10-21-2010, 12:01 PM

I need to create some code to prompt a user to provide a password to save/save as. I have a costing model on excel which needs to be shared by our sales reps. on our network server, but not saved anywhere else.

Apologies if already posted, but i have been trawling the site and found nothing suitable

Thanks in advance

The Cat

**blane245** · 10-21-2010, 02:32 PM

I think this is what you want. It asks the user for a password right before the file is saved. Put it in the thisWorkBook object of VBA.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim thePass As String
    thePass = Application.InputBox("Enter Password", "Password Request", "", , , , , 2)
    Me.Password = thePass
End Sub

**The_Cat** · 10-22-2010, 05:01 AM

Thanks Blane
Although this code askes for the same password to open and the prompt to save can be cancelled and the file saved anyway

**blane245** · 10-22-2010, 08:18 AM

here's a version that allows you to cancel the save. The first executable line will skip
asking for a password if there already is one. You can comment that out if you wish.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    ' if the file already has a password, then just proceed with save
    If (Me.Password <> "") Then Exit Sub
    
    Dim thePass As Variant
    
    ' ask the user for the password for this file
    thePass = Application.InputBox("Enter Password", "Password Request", "", , , , , 2)
    
    ' if InputBox returns a string value, then the user
    ' provided a password, so save the password and
    ' do not cancel the save action
    If (VarType(thePass) = vbString) Then
        Me.Password = thePass
        Cancel = False
        
    ' user canceled the password dialog - cancel the save action
    Else
        Cancel = True
    End If
End Sub