Macro for avoiding unauthorized duplication for excel file

[gestioperformance]

Hi everybody,
i'm having issues with the following macro that i want to include in a spreadsheet to prevent the user to give the file to unauthorized third parties. basically, i want to make the file readable only by a specific "computer name".


hereby the macro:



Private Sub Workbook_Open()
Dim cella As Range
Set cella = Sheets(1).Cells(Rows.Count, Columns.Count)
If cella = "" Then
cella = Environ("computername")

ActiveWorkbook.Save
Exit Sub
End If
If cella <> "PC NAME" Then ' username Vale
MsgBox "Unauthorized access", vbCritical, "Access Denied"


ActiveWindow.Close
End If
Set cella = Nothing
End Sub








Thank you in advance for any kind of help.

 

[rlv01]

Re: Help: macro for avoiding unauthorized duplication for excel file

That's not a very secure method. All the unauthorized user has to do is hold down the shift key when opening the file to disable macros.

 

[gestioperformance]

Re: Help: macro for avoiding unauthorized duplication for excel file

That's not a very secure method. All the unauthorized user has to do is hold down the shift key when opening the file to disable macros.

i know, but i'm quite confident that the user does not have the knowledge of doing this kind of workaround.



The macro i wrote still gives me some problems: after the first correct opening of the file, i receive the error as if i'm not authorized to view the file, even if i've insterted my computer name in the code.
what am i missing?

Thanks

 

[rlv01]

Re: Help: macro for avoiding unauthorized duplication for excel file

So something like this then

Private Sub Workbook_Open()
    Dim cella As Range
    Set cella = Sheets(1).Cells(Rows.Count, Columns.Count)
    Select Case cella.Value
    Case ""    'Set Initial Value
        cella = Environ("computername")
        ActiveWorkbook.Save
        Exit Sub
    Case "Admin"    'Admin user
    Case Environ("computername")    'Authorized user
    Case Else    'Unauthorized user
        MsgBox "Unauthorized access", vbCritical, "Access Denied"
        ActiveWindow.Close
    End Select
    Set cella = Nothing
End Sub

 

[gestioperformance]

Re: Help: macro for avoiding unauthorized duplication for excel file

So something like this then

Private Sub Workbook_Open()
    Dim cella As Range
    Set cella = Sheets(1).Cells(Rows.Count, Columns.Count)
    Select Case cella.Value
    Case ""    'Set Initial Value
        cella = Environ("computername")
        ActiveWorkbook.Save
        Exit Sub
    Case "Admin"    'Admin user
    Case Environ("computername")    'Authorized user
    Case Else    'Unauthorized user
        MsgBox "Unauthorized access", vbCritical, "Access Denied"
        ActiveWindow.Close
    End Select
    Set cella = Nothing
End Sub

Thank you very much!
Since, as you said, this isn't a very good method to avoid the access to unauthorized users, do you have any suggestion for a stronger security measure?

Thank you again!

 

[rlv01]

Re: Help: macro for avoiding unauthorized duplication for excel file

Well, you cold find out the computer name and pre-load it so that a blank cell is also an illegal value. Keep in mind, anything that can be done via macro, can be undone by not enabling macros so really you are depending on the ignorance of the user which is not the same as security. I would additionally require a password to open the workbook to have something not based on macro operation. Per your scheme above, you could instead use VBA (GetSettings, SaveSettings) to store the name in the registry instead of the last cell on the worksheet.

 

[gestioperformance]

Re: Help: macro for avoiding unauthorized duplication for excel file

Hi everybody,

I should add a command to my macro to open a new file after the check of the computter name. The command I found is:


Sub open_file ()
Workbooks.Open Filename:="C:NavKeys.xls", password:="123"
End sub.


but I don't know how I could use it.


Thanks in advance for your help.

 

[rlv01]

Re: Help: macro for avoiding unauthorized duplication for excel file

but I don't know how I could use it..

How do you want to use it?

 

[gestioperformance]

Re: Help: macro for avoiding unauthorized duplication for excel file

i need to verify the user of the pc and then open another file

 

[rlv01]

Re: Help: macro for avoiding unauthorized duplication for excel file

There is nothing fundamentally wrong with what you posted earlier, except for some minor syntax errors. Code like this will open a password protected workbook.

Sub open_file()
    'Replace "C:\Users\SomeUser\Documents\" below with the path to YOUR file
    Workbooks.Open Filename:="C:\Users\SomeUser\Documents\NavKeys.xls", Password:="123"
End Sub

Can you explain the problem you are having with using it?