Protect excel files to open only on designated computers

[daavis]

Hi,
Is it possible to protect an excel file such that it will open up only on designated computers (identified by the computer name or some unique hardware identification like MAC address etc)?
I was wondering if the VB editor can be used to do the same.

Let me put my requirement in detail:

I have an excel file "123" created in one computer (named=A). On this computer this file can be opened by anyone.
I write a code such a way that, this particular file when copied on to other computers say (B,C & D) would open up as usual. But on computer E or any other computer, it should not open.

I cannot use password protect feature on the file as "n" number of users will be accessing this file on those designated computers.

The reason for this request was, I was finding few of the clients copying the files on their personal drives or email without proper consent.

If its possible, I would like to employ the same on few of my word (.doc) files as well.

Thanks in advance for any help in this regard.

[royUK]

Whatever code that you use it won't stop copying files by dragging & dropping.

Such code can easily be overcome by not enabling macros

[daavis]

@RoyUK, Thank you for your comments.
As you said I cannot prevent dragging and dropping. All i want is even if they do, they should not be able to view my data on other computer.
Like you said, codes can be overcome by not enabling macros. I tried the option suggested by elprup for the issue "Macro Security - Digital Certificates". it works that way to force a person to enable macro.
I created the code in Excel2003 and opened the file using 2007 and still it worked.

Now with this code in place, I can force an unauthorised person to enable macro.

So if someone could suggest me a way to solve my initial query, I would be very grateful.

@RoyUK, if you can suggest me some idea or link me to some person who can guide me, I will be obliged.

[Deamo]

The Environ function has a variable called COMPUTERNAME which may be of use?

perhaps:

Please Login or Register  to view this content.

[snb]

or

Please Login or Register  to view this content.

[daavis]

Hi all thanks for the suggestions. But I am slightly confused.

@Deamo, in your code if the computer name is "A", then access is denied and workbook will close. Am i correct? or is it otherwise. I can probably work it out :-).
@snb, thanks for the alternative code. does this code help me verify with the computer name or verify some unique id like the MAC address? and where do i place this code. i am a beginner with VBE :-)

Sorry for asking this silly question, can the computer name and username be same? which name does this code verify?
Is verifying the computer name a better option or some other unique id is better (MAC address or HD- Serial No. etc) ?
Kindly suggest, I want to make things fool proof.

[royUK]

Here'scodetoget the computer name

Please Login or Register  to view this content.

Here's an example to force users to enable macros,you can add the code above & use a Select Case to check if the computer is approved

[royUK]

Here's an example that does it for you, you will need to open with macros disabled then edit the list of approved computers.

[spenser723]

RoyUK,

Thanks for the file. It does not open on my PC with macros enabled. When I open the file you sent with macros disabled, the only code that appears is the following:

Option Explicit

Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" _
(ByVal Buffer As String, _
Size As Long) As Long

Function GetMachineName() As String
Dim strBuf As String * 16, strPcName As String, lngPc As Long

lngPc = GetComputerName(strBuf, Len(strBuf))
If lngPc <> 0 Then
strPcName = Left(strBuf, InStr(strBuf, vbNullChar) - 1)
Debug.Print "Your Name of Computer is :" & strPcName
GetMachineName = strPcName
Else
Debug.Print "Error"
End If
End Function


Where can I insert my computer name?

Thanks!

[bendcox]

Hi All,

Is it possible to protect a file in such a way that a user can open the file and track changes to update it to its latest iteration, but cannot move or update cells in any way. I have a schedule spreadsheet that my factory operatives could do with seeing, but I don't want them tinkering and saving!

Cheers

[arlu1201]

bendcox,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[shaileshpatel]

Option Explicit
Option Compare Text
Dim oSht As Object '<-includes Chart Sheets
Private Const sMacroSheet As String = "sheet1" 'Enter name of the Entry/Warning Page
Private Sub Workbook_Open()

Select Case GetMachineName()
Case "A", "B", "C", "D" '<- list authrised PC names here
Sheets(sMacroSheet).Select
For Each oSht In ActiveWorkbook.Sheets
oSht.Visible = True
Next
Sheets(sMacroSheet).Visible = xlSheetVeryHidden
DisableCutAndPaste
Case Else
ThisWorkbook.Close False
End Select
End Sub
Private Sub Workbook_Activate()
DisableCutAndPaste
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each oSht In ActiveWorkbook.Sheets
If sMacroSheet = oSht.Name Then
oSht.Visible = xlSheetVisible
Else: oSht.Visible = xlSheetVeryHidden
End If
Next
EnableCutAndPaste
End Sub

Private Sub Workbook_Deactivate()
EnableCutAndPaste
End Sub

in this VBA code where to add computer name.

[shaileshpatel]

i had tried add computer name here
"Select Case GetMachineName()
Case "A", "B", "C", "D" '<- list authrised PC names here"
but it not work please help me for solving the problem

[FDibbins]

shaileshpatel, perhaps you missed the post immediately above yours? If so, here it is again...

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[bjj]

but debug file

[clink.ds]

I tried this but after applying even the computer name I gave on that also error is coming. can you pl guide or share complete code / example

[FDibbins]

I tried this but after applying even the computer name I gave on that also error is coming. can you pl guide or share complete code / example

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread