Protection

[elbergeron]

Part of my consulting is to allow my clients to use an excel workbook that I have spent literally thousands of hours creating and updating. I can protect the content, is there any way that I can make the workbook look for an identifier on the users computer before they can open it? I don't want it to be copied and used on other computers or given to associates without my permission.

Thanks

 

[tviman]

You can't do this from within Excel. You'd have to create a "logon script" (for lack of better terms) that would search for the identifier and then open Excel.

I'm sure you know that you can password protect the workbook but there would be nothing stopping your clients form passing on the password.

Regardless of what you do, until you create a "license agreement" for clients who use it, you'd just be treating the symptom and not the cause of the affliction.

There's always a better way. The fun is trying to find it!

 

[fumei]

I you are willing to hard code the usernames, you could get the current system login name, compare to your list, and if it doesn't match...put up a little message saying sorry, but byebye, and close the file.

Of course you would have to know what those names are.

Gerry

 

[elbergeron]

I actually DO know all the names of the users (employees), and whats a couple more hours! Would you mind sharing how I would do this?

Lance

 

[Molby]

a$ = Environ("USERNAME") will give you the Username of the current user in a$, if that's any use.

 

[fumei]

This closes Excel on failure. You may not want that. The person attempting to open this may have other files open - although they would be prompted. In any case it would be something like this.

Private Sub Workbook_Open()
Dim CheckNames(2) As String
Dim strCurrentUser As String
Dim var
Dim i

CheckNames(0) = "blah.blah1"
CheckNames(1) = "blah22.blahhhh"
CheckNames(2) = "bob.bob"
'   etc. etc.

strCurrentUser = Environ("username")
On Error Resume Next
For var = 0 To 2   ' or whatever the count is of the array
    If strCurrentUser = CheckNames(i) Then
        Exit Sub
    Else
        i = i + 1
    End If
Next
    MsgBox "Sorry, you are not authorized to open this file."
    Application.Quit  ' note that this closes Excel!
End Sub

Gerry

 

[fumei]

OH! And don't forget to include YOUR name in the list!!!!

Gerry

 

[macropod]

There is little you can do to protect the contents of an worksheet once the user's got it open. And macro-based protection to prevent a workbook being opened by the wrong person is easily defeated by holding down the shift key while the workbook opens.

If you're not prepared to work with the above limitations, then you may need a non-Excel solution.

If you're prepared to work with the above limitations, however, you could put a lot of critical information and formulae into your vba code instead of in the workbook itself, thereby preventing the workbook from functioning correctly if user stops the macro from running. Similarly, you macro could be set to display certain restricted worksheets that wouldn't be available if the macro was prevented from running. You'd probably need to couple these measures with intercepts of the save routines, so that user's couldn't save the workbook with the bits you're trying to protect exposed.

Cheers