protect workbook for only one PC

Hi mchoud

Not sure that this can be done, directly at at least. On way I can think of would be to make all but one sheet XlVeryHiden, then have some code in the Workbook Open event that checks the Application.UserName, if it's one that should not read the Workbook then they only see the one sheet (with a message on it). If they user is ok to read then t shows all sheets and hides the message sheet. If you follow This Link

You can download Microsoft Excel Examples

This will show you how to use API to return the Retrieve the logged on user name.

Kholid,
This should work...
...Place this code in a Module
'Code Start
Option Explicit
Declare Function GetComputerName& Lib "kernel32" _
Alias "GetComputerNameA" (ByVal lbbuffer As String, nsize As Long)
Function ReturnName()
Dim z As String * 64
Call GetComputerName(z, 64)
ReturnName = z
End Function

Sub WhatIsMyComputerName()
Dim CpuName As String
Application.DisplayAlerts = False
CpuName = Left(ReturnName(), 11)
Range("A1").Value = CpuName
End Sub
'Code End

Place this code in the "ThisWorkBook" Module...

'Code Start
Option Explicit
Private Sub Workbook_Open()
Dim CpuName As String
Application.DisplayAlerts = False
CpuName = Left(ReturnName(), 11)

If CpuName <> "DEO-C7OLZGZ" Then
ActiveWorkbook.Close
End If
Application.DisplayAlerts = True
End Sub
'Code End

You will need to run the WhatIsMyComputerName Sub to find your computer name, then change line...
If CpuName <> "DEO-C7OLZGZ" Then
in the Private Sub Workbook_Open() to match your computer name.

Option Explicit
Private Declare Function GetVolumeInformation Lib "kernel32.dll" Alias _
    "GetVolumeInformationA" (ByVal lpRootPathName As String, ByVal lpVolumeNameBuffer As String, _
      ByVal nVolumeNameSize As Integer, lpVolumeSerialNumber As Long, lpMaximumComponentLength As Long, _
      lpFileSystemFlags As Long, ByVal lpFileSystemNameBuffer As String, ByVal nFileSystemNameSize As Long) As Long




Private Sub Workbook_Open()
    Dim lngSerialNumber As Long
    Dim fso As New FileSystemObject
    Dim strDrives(1) As String
    lngSerialNumber = -4462345
    strDrives(0) = "C:\"
    strDrives(1) = "C"
    'use API and doublecheck using Scripting Runtime
    If GetSerialNumber(strDrives(0)) = lngSerialNumber Then
        'EnableProject
    ElseIf fso.GetDrive(strDrives(1)).SerialNumber = lngSerialNumber Then
        'EnableProject
    Else
        MsgBox "Warn user here."
    End If
    Set fso = Nothing
End Sub




Private Function GetSerialNumber(strDrive As String) As Long
    Dim SerialNumber As Long, lngReturn As Long
    Dim str1 As String, str2 As String
    
    str1 = String$(255, Chr$(0))
    str2 = String$(255, Chr$(0))
    lngReturn = GetVolumeInformation(strDrive, str1, _
    Len(str1), SerialNumber, 0, 0, str2, Len(str2))
    
    GetSerialNumber = SerialNumber
End Function

The above is part of a complicated and overdone method to protect your workbook from being run on any other machine. I had to devise a way to keep three workbooks on networked computers from being opened on any other machine than the one it was customized to run on. So...similiar to your goal here, I think. This is part of the code. If you'd like I'll mail you the main book which stores the userbook in binary format in a standard module. The workbook is created from the data stored in the module. Seeing that macros must be enabled to even create the workbook and then open it, you do not have to worry about hiding sheets. This is a poor method if the workbook must be saved often and is larger than about 2 megs on a decent system. You will need to set a reference to Microsoft Scripting Runtime to use the code above. It checks for the volume serial number using the Win32 API first. If this fails, it will doublecheck using the API wrapper in the Scripting Runtime methods and properties.

There should be a forward slash after the C: strDrives(0) = "C:"

Tom

Hi,

The mainproblem with the solutions above is that they can be override in a simpel way - which I'm not gonna view here :wink2:

However, all problems are subject to users level of skillness & knowledge.

Have also a look on the file proporties via the Explorer

NIce solution but remember to put in some code to deal with Macro's being disabled. In other words the above code relies on macro's not being disabled.
There have been several postings here discussing ways to make sure user has macro's enabled and that should be incorporated here.

Hi Nimrod. I don't know who's solution you were referring to? The one I posted will simply not give access to the workbook unless macros are enabled. Workbook A stores wb B in binary format in a standard module. If the serial does not match or macros were not enabled to begin with then the workbook cannot be created and therefore opened. If the serial does match then workbook B is created and wb A goes away. It is much more complicated than what I posted. Think to yourself how such a workbook might be saved? That is why I think it is too complicated and the only reason I still use it is because I was ignorant enough to create it in the first place. But with all the lengths I went to above the workbook is still only as secure as the VBA project's security. So, yes, more secure than a workbooks usual security but still, as Dennis noted, not very secure at all.

Tom

Ive posted much on this topic and must say I love TsToms VBA, ill have such fun with that one for sure and before I forget pass a very big thanks personally from Jack, I will be laughing loudly at work if I get that one working.

As I say I have made no like this and don’t be falsely assured any Excel or VBA related fix is safe, I can nearly always get in pretty quick. Only the password to open the document takes time, thus my interest in TsToms post.

Check my older posts for more an the guys who have added to these feed very rich stuff to read,

I’m still – even more so now convinced PGP is the software to wrap documents in to 128bit arithmetic build, which carries private and public keys. I’m playing with this one, and will update soon,.

At work ill be amazed if many can unpick the simpler methods, or you would know how they are, but there shinning skills, us Excelers cant stop being flash and showing of with some wonder or the other, you should spot who to watch.

Jack