Password protect workbook when opened. On failed attempt close, send email and delete workbook.

[camerong]

Hi guys,

Not sure where to start with this one, any help is greatly appreciated.

I am after a code that would run as soon as the workbook is opened. I would like it to first check the "Device name" and compare it to a list of names in the code which are allowed to open the workbook. If the "Device name" is on the list then it opens fine without any password prompt. If the "Device name" is not on the list then I would like it to prompt the user for a password (set in the code). If the user inputs the correct password, then the workbook opens fully. If the user inputs the incorrect password, then the Workbook shuts down, is deleted (would be great if it was permanently deleted, not just put in recycling bin) and an email is sent to a specific email address (stipulated in the code).

I am after this to try and protect company intellectual property.

 

[sijpie]

You can do it this way. Not very difficult. But it is no match for a user with extensive Excel knowledge. I would have the file open in less then a few minutes.
If you really want to protect intellectual property, then you will need a software solution such as you see with commercial software. And yes, that will cost something, but it will work..

If you want to continue the way you propose, i can write some code to do just that.

 

[camerong]

Hi sijpie,

That'd be fantastic if you could put some code together to do it.

We may look at commercial software in the future, but for now we would like to go with my original post if possible.

Thank you

 

[sijpie]

I'll see if I can get something to you by early next week

 

[Kyle123]

The biggest issue with this (despite it being impossible really to make Excel secure) is that the user could simply disable macros and then none of the protection code would run

 

[Candyman8019]

I typically make all but one sheet 'very hidden' until the user is authenticated...that way it is useless if they disable macros.

 

[Kyle123]

Unless they unhide the sheet in the VBE. I think it probably depends what the intellectual property actually is though. If it's VBA code, there's pretty much no way of making it secure whilst still within an Excel workbook. Even the commercial solutions don't solve it without wrapping the workbook in an EXE.

I'd just stick a password on it and the VBA project and call it a day!

 

[Candyman8019]

At the end of the day, if someone wants the info...they'll get it, no matter how secure the solution or network...it's just a matter of time. It could be a hack...could be screenshots...it could be taking pics with a cellphone...anything. I can't speak for everyone, but for myself, making the workbooks secure enough to protect from the majority of users is acceptable.

 

[camerong]

I'll see if I can get something to you by early next week

Thanks Sijpie

Thanks guys

 

[sijpie]

OK, there are four bits to this:

1. a userform, which you will have to create, see image below
2. the code for the userform
3. the code for a standard module
4. the code for the ThisWorkbook module

Read through the comments, particularly the one starting with <<<< in the WorkBook_Open() event and the starting comments in the Userform code and the standard module

1. Simple userform, one Textbox, two Buttons. In the properties of the Enterbutton, set the 'Default' to true. the user can then just press Enter after filling out the password.

2. Code for the userform

Option Explicit

Private Const ciAttemptAllowed As Integer = 3   'set to maximum allowed tries
Private iAttempt As Integer

Private Sub CommandButton1_Click()
    If TextBox1.Value Like csPWd Then
        FullyOpenWb True
        Unload Me
    Else
        iAttempt = iAttempt + 1
        If iAttempt > ciAttemptAllowed Then
            CommandButton2_Click
        Else
            TextBox1.Value = ""
            TextBox1.SetFocus
        End If
    End If
End Sub

Private Sub CommandButton2_Click()
    Unload Me
    ClearWorkbook True
    
End Sub

3. Code for a standard module

Option Explicit

Public Const csPWd As String = "MyPassWord"
Public Const csAlwaysShowSheet As String = "StartingSheet"  'This is the sheet name not to be extremely hidden
 
Sub ClearWorkbook(bDummy As Boolean)        'this 'Dummy' parameter ensures the macro is not shown when pressing F8 in the workbook
' delete workbook from disk and close
    Dim sPathName As String
    
    With ThisWorkbook
        sPathName = .FullName
        
        'delete without file going to Bin
        'pretend it is saved and allow it to be killed
        .Saved = True
        .ChangeFileAccess xlReadOnly
        'delete file
        Kill sPathName
        .Close savechanges:=False
    End With
End Sub

Sub FullyOpenWb(bDummy As Boolean)
    Dim wsWS As Worksheet
    Dim chCH As Chart
    
    'unhide all sheets & charts
    Application.ScreenUpdating = False
    For Each wsWS In Worksheets
        wsWS.Visible = xlSheetVisible
    Next wsWS
    For Each chCH In Charts
        chCH.Visible = xlSheetVisible
    Next chCH
    Application.ScreenUpdating = True
    
End Sub

Sub FullyHideSheets(bDummy As Boolean)
    Dim wsWS As Worksheet
    Dim chCH As Chart
    
    'hide all sheets & charts other than starting sheet
    Application.ScreenUpdating = False
    For Each wsWS In Worksheets
        If Not wsWS.Name Like csAlwaysShowSheet Then
            wsWS.Visible = xlSheetVeryHidden
        End If
    Next wsWS
    For Each chCH In Charts
        chCH.Visible = xlSheetVeryHidden
    Next chCH
    Application.ScreenUpdating = True
    
End Sub

4. The code for the ThisWorkbook module. To open the ThisWorkbook module, double click on the ThisWorkbook for the workbook in the top left window of the VBA editor

Option Explicit

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
'This runs after the file has been saaved. Display the hidden sheets again
    FullyOpenWb True
    'tell excel the file has been saved (as we just changed it)
    Me.Saved = True
End Sub



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'This runs just before the file gets saved. Hide all the sheets

    FullyHideSheets True
End Sub

Private Sub Workbook_Open()
    ufGetpw.Show    '<<<<< I have named the Userform ufGetpw. _
                    If you don't rename your userform, then it _
                    will be Userform1 or similar. Change that here
End Sub

test it on a copy of the workbook!!!! Once the macro deletes the file it is gone, no getting it back!!!!

Once you are happy, make sure to protect the VBA with a password before sending it out to others. Use a different password for that !!