Allow Use Of Workbook On Specific Computers Only

arttocad · Jun 6th 2008

I have a workbook with sensitive company information. I need a way to validate the computer the workbook is being opened on and then close the workbook if it is not a confirmed workstation.

The file is already password protected on opening, each worksheet and the workbook is protected, and the VBA code is protected. We just need to take it one step further.

Is there a way to check a registry entry or the computers MAC address on workbook_open?

I have found code to close the workbook without saving which I would use after a message box stating "You do not have permission to access this file."

If someone has their macro security level set to high or very high, that prevents my code from running. How can I make this work on any computer no matter what their macro settings are?

Any help would be greatly appreciated.

Dave Hawley · Jun 6th 2008

Re: Allow Use Of Workbook On Specific Computers Only

Yes, with VBA. BUT, the user can choose disable macros or have their security pre-set to High, so it's not at all reliable.

Why not make the Workbook Read Only unless they know the password?

arttocad · Jun 6th 2008

Re: Allow Use Of Workbook On Specific Computers Only

If the file was inadvertently or intentionally e-mailed to one of our competitors or an employee felt the need to take it with them when they left, we don't want the file to be usable.

I understand that most of these types of protection may only thwart mid to low level excel users but I have been put to the task of solving this problem. Maybe there are some creative tricks to work around the macro security issue.

Is it possible to have the contents of the file only visible when a macro is running maybe? That way, even if they had their security level set high, they wouldn't see anything.

h1h · Jun 6th 2008

Re: Allow Use Of Workbook On Specific Computers Only

I do something similair but with the User Name. I use this function:

Code

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function UserName() As String
    Dim Buffer As String * 100
    Dim BuffLen As Long
    BuffLen = 100
    GetUserName Buffer, BuffLen
    UserName = Left(Buffer, BuffLen - 1)
End Function

Dave Hawley · Jun 7th 2008

Re: Allow Use Of Workbook On Specific Computers Only

Quote

Maybe there are some creative tricks to work around the macro security issue.

No, and asking such question is in violation of the rules.

[email protected] · Jun 7th 2008

Re: Allow Use Of Workbook On Specific Computers Only

Sounds more like a security-level access question that the network administrator at your company might solve. (ie: Under Windows, create a special policy group containing the users who have rights to open/modify/delete the file, and enforce the policy to said file)

(of course, that's if memory serves, as I haven't done that in 8 years) Hehehe!

Simon Lloyd · Jun 7th 2008

Re: Allow Use Of Workbook On Specific Computers Only

one other way is to make the workbook an exe file, there are programs out there that will do that, of course the easiest solution would be to have all the worksheets xlVeryHidden except a welcome sheet, that way if they dont enable macro's they only see the welcome sheet and cannot unhide the other sheets via the tool bar they can only be made visible by code!

[email protected] · Jun 8th 2008

Re: Allow Use Of Workbook On Specific Computers Only

Ah, see I didn't know that one... I'll try it out myself... Thanx!

Simon Lloyd · Jun 8th 2008

Re: Allow Use Of Workbook On Specific Computers Only

You would do something like:

Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sht As Worksheet
Sheets("Welcome").Visible = True
For Each Sht In Sheets
If Sht.Name = "Welcome" Then
Else
Sht.Visible = xlVeryHidden
End If
End Sub


Private Sub Workbook_Open()
Dim Sht As Worksheet
If Environ("computername") <> "MACHINE NAME" Then Exit Sub
For Each Sht In Sheets
Sht.Visible = True
End If
Sheets("Welcome").Visible = False
End Sub

Display More

where "MACHINE NAME" would be the actual name of your computer i.e "Warriors PC"

arttocad · Jun 10th 2008

Re: Allow Use Of Workbook On Specific Computers Only

Thank you for your replies.

Simon, that looks like it will do the trick perfectly. I will have to try that out today.

[email protected], this request has come from my IT department so I think they either don't know another way to accomplish this or they want it done a certain way...

Dave, sorry, don't mean to disrespect you or the rules but I was not asking for a way to disable macro security. I was asking for creative ways to work around the macro issue which it looks like Simon has provided.

h1h, I tried using your code and I must be doing something wrong cause it didn't seem to work.

KUEI · Aug 5th 2015

Re: Allow Use Of Workbook On Specific Computers Only

Quote from Simon Lloyd;441226

You would do something like:

Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sht As Worksheet
Sheets("Welcome").Visible = True
For Each Sht In Sheets
If Sht.Name = "Welcome" Then
Else
Sht.Visible = xlVeryHidden
End If
End Sub


Private Sub Workbook_Open()
Dim Sht As Worksheet
If Environ("computername") <> "MACHINE NAME" Then Exit Sub
For Each Sht In Sheets
Sht.Visible = True
End If
Sheets("Welcome").Visible = False
End Sub

Display More

where "MACHINE NAME" would be the actual name of your computer i.e "Warriors PC"

I tried your example, but had problems with it's execution. I made modifications and added a security bypass password. Your example was great at getting me to understand how this works. It just needed some tweaks. My (and your) code is below. Thanks for the help!!!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sht As Worksheet
Sheets("ENTER").Visible = True 'ENTER is the name of the sheet I want visible when opening Excel workbook
Sheets("ENTER").Select
For Each Sht In Sheets
If Sht.Name = "ENTER" Then

Else
Sht.Visible = xlVeryHidden 'Hide each sheet that is not named ENTER
End If
Next Sht
End Sub

Private Sub Workbook_Open()
Dim Sht As Worksheet
Dim Pass

If Environ("computername") <> "GOOBERS-PC" Then 'EXAMPLE...GOOBERS-PC is the name of the computer in windows...
' ...to find computer name, right click on Computer icon and look for "Computer name:", or...
' ...Control Panel, then System and look for "Computer name:"

For Each Sht In Sheets
If Sht.Name <> "ENTER" Then
Sht.Visible = xlVeryHidden
End If
Next Sht
MsgBox "Incorrect Machine Config...Email &amp;quot;[email protected]&amp;quot;[/email]" ' OPTIONAL PASSWORD TO BYPASS SECURITY-START HERE v
Pass = InputBox("Who Are You?", "Who Box")
If Pass = "obamaSUX" Then 'obamaSUX is the password to jup security
For Each Sht In Sheets
Sht.Visible = True
Next Sht
Sheets("MAIN").Select ' MAIN is the name of the sheet I want to jump to after entering password
Range("A1:N1").Select
Else: Exit Sub
End If ' - END HERE ^

Else
For Each Sht In Sheets
Sht.Visible = True
Next Sht
End If

End Sub

Sub ENTER1()
' Form control button on sheet named ENTER to jump to sheet named MAIN ...
'...when entering workbook
' ENTER1 Macro
'

'
If Environ("computername") <> "GOOBERS-PC" Then
MsgBox "Incorrect Machine Config...Email &amp;quot;[email protected]&amp;quot;[/email]"

Else
Sheets("MAIN").Select
Range("A1:N1").Select
End If

End Sub

**pike** · Aug 6th 2015

Re: Allow Use Of Workbook On Specific Computers Only

KUEI
Please take a few moments to read the forum rules
Start your own thread and add code tags to your post

Allow Use Of Workbook On Specific Computers Only

Participate now!

Share