Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

why don't you use a password?

"Sean" wrote:

Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Try:

Private Sub Workbook_Open()
res = InputBox("Please enter your Password....", "Enter your Password in the
box here.")
If res = "1234" Then
Sheet1.Activate
Else
If res < "1234" Then MsgBox "You are NOT permitted to Open this File ! !",
xlExclamation
Application.DisplayAlerts = False
Application.Quit
End If
End Sub


Corey....


"Sean" wrote in message
oups.com...
Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Can't use a password as the user who is allowed just e-mails it to
someone who shouldn't have it and just tells the password

Mike wrote:

why don't you use a password?

"Sean" wrote:

Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

How about:

Private Sub Workbook_Open()
With Application
If .UserName = "Fred Flintstone" Then
Else
MsgBox "You are NOT permitted to Open this File ! !", xlExclamation
Application.DisplayAlerts = False
Application.Quit
End If
End With
End Sub


Corey....

"Sean" wrote in message
ups.com...
Can't use a password as the user who is allowed just e-mails it to
someone who shouldn't have it and just tells the password

Mike wrote:

why don't you use a password?

"Sean" wrote:

Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Thanks Corey that looks pretty good. How would I include Wilma in the
allowed list?

I assume the Username is the computers logged in User??


Corey wrote:

How about:

Private Sub Workbook_Open()
With Application
If .UserName = "Fred Flintstone" Then
Else
MsgBox "You are NOT permitted to Open this File ! !", xlExclamation
Application.DisplayAlerts = False
Application.Quit
End If
End With
End Sub


Corey....

"Sean" wrote in message
ups.com...
Can't use a password as the user who is allowed just e-mails it to
someone who shouldn't have it and just tells the password

Mike wrote:

why don't you use a password?

"Sean" wrote:

Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Not really.

Any suggestion will probably include macros. Macros can be disabled and your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

One way is to put the list of users in the workbook somewhere, ie, a
worksheet named "Users" The worksheet can be hidden from view but easily
accessed to update the list without changing the code. Make the list a Named
Range like "MyUsers" (not the same as a worksheet name). This list will be
added to an array and will be compared to the .UserName of the person trying
to open the workbook.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName ), myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !", xlExclamation
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

This is untested, so try it out or modify as needed.
Mike F
"Sean" wrote in message
oups.com...
Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Thanks Mike, I'm getting a Syntax error on line
If IsError(.Match(.UserName ), myArray, 0)) Then

Mike Fogleman wrote:

One way is to put the list of users in the workbook somewhere, ie, a
worksheet named "Users" The worksheet can be hidden from view but easily
accessed to update the list without changing the code. Make the list a Named
Range like "MyUsers" (not the same as a worksheet name). This list will be
added to an array and will be compared to the .UserName of the person trying
to open the workbook.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName ), myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !", xlExclamation
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

This is untested, so try it out or modify as needed.
Mike F
"Sean" wrote in message
oups.com...
Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Dave has a valid point. There is a work-around for those who disable macros,
using a "Splash" sheet, if you're interested.

Mike F
"Dave Peterson" wrote in message
...
Not really.

Any suggestion will probably include macros. Macros can be disabled and
your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

Right you are! Parentheses wrong order.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName, myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

Mike F
"Sean" wrote in message
oups.com...
Thanks Mike, I'm getting a Syntax error on line
If IsError(.Match(.UserName ), myArray, 0)) Then

Mike Fogleman wrote:

One way is to put the list of users in the workbook somewhere, ie, a
worksheet named "Users" The worksheet can be hidden from view but easily
accessed to update the list without changing the code. Make the list a
Named
Range like "MyUsers" (not the same as a worksheet name). This list will
be
added to an array and will be compared to the .UserName of the person
trying
to open the workbook.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName ), myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !",
xlExclamation
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

This is untested, so try it out or modify as needed.
Mike F
"Sean" wrote in message
oups.com...
Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

I don't think there is any workaround to the user disabling macros.

Once the workbook is opened, the user can show hidden sheets and unprotect any
worksheet (or the workbook).

And if the user knows how to use google, the user can find a way to bypass the
project protection.

If you really have sensitive data, don't put it into excel. If you have to put
it into excel, don't share it with anyone you don't trust.

Mike Fogleman wrote:

Dave has a valid point. There is a work-around for those who disable macros,
using a "Splash" sheet, if you're interested.

Mike F
"Dave Peterson" wrote in message
...
Not really.

Any suggestion will probably include macros. Macros can be disabled and
your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

--

Dave Peterson

Thanks Mike, it works great. How would I include that it opens on a
Blank Sheet, say Sheet1 if I get the message "You are not permitted"
etc and if you don't it goes to Sheet2 A1

Slight problem I see is that a non-permitted user may see info
displayed on the screen and it won't close until they click ok to the
message


Mike Fogleman wrote:

Right you are! Parentheses wrong order.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName, myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

Mike F
"Sean" wrote in message
oups.com...
Thanks Mike, I'm getting a Syntax error on line
If IsError(.Match(.UserName ), myArray, 0)) Then

Mike Fogleman wrote:

One way is to put the list of users in the workbook somewhere, ie, a
worksheet named "Users" The worksheet can be hidden from view but easily
accessed to update the list without changing the code. Make the list a
Named
Range like "MyUsers" (not the same as a worksheet name). This list will
be
added to an array and will be compared to the .UserName of the person
trying
to open the workbook.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName ), myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !",
xlExclamation
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

This is untested, so try it out or modify as needed.
Mike F
"Sean" wrote in message
oups.com...
Is it possible to deny access to open an Excel file except to specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

You're right Dave, the best we can do is keep honest people honest.

Mike F
"Dave Peterson" wrote in message
...
I don't think there is any workaround to the user disabling macros.

Once the workbook is opened, the user can show hidden sheets and unprotect
any
worksheet (or the workbook).

And if the user knows how to use google, the user can find a way to bypass
the
project protection.

If you really have sensitive data, don't put it into excel. If you have
to put
it into excel, don't share it with anyone you don't trust.

Mike Fogleman wrote:

Dave has a valid point. There is a work-around for those who disable
macros,
using a "Splash" sheet, if you're interested.

Mike F
"Dave Peterson" wrote in message
...
Not really.

Any suggestion will probably include macros. Macros can be disabled
and
your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

--

Dave Peterson

Well I'm pretty pleased with the attached which will open at a Blank
sheet if its a non-permitted user, otherwise it opens on an info sheet.
One slight problem is that if its a non-permitted user the whole Excel
application closes, which I wouldn't want if other active files are
open. How would I just close the file I'm attempting to open?

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.GoTo Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Input").Select
Application.ScreenUpdating = True

Dim myArray As Variant
Dim arName As String


arName = "Users"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value


With Application
If IsError(.Match(.UserName, myArray, 0)) Then
ThisWorkbook.Sheets("Blank Sheet").Select

MsgBox "You are NOT Permitted to access this File " & vbNewLine & _
" " & vbNewLine & _
"Please Contact " & vbNewLine & _
" " & vbNewLine & _
"Joe Bloggs at " & vbNewLine & _
" " & vbNewLine & _
"ABC Group +0019 66200000"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
ThisWorkbook.Sheets("Input").Select

End Sub







Mike Fogleman wrote:

You're right Dave, the best we can do is keep honest people honest.

Mike F
"Dave Peterson" wrote in message
...
I don't think there is any workaround to the user disabling macros.

Once the workbook is opened, the user can show hidden sheets and unprotect
any
worksheet (or the workbook).

And if the user knows how to use google, the user can find a way to bypass
the
project protection.

If you really have sensitive data, don't put it into excel. If you have
to put
it into excel, don't share it with anyone you don't trust.

Mike Fogleman wrote:

Dave has a valid point. There is a work-around for those who disable
macros,
using a "Splash" sheet, if you're interested.

Mike F
"Dave Peterson" wrote in message
...
Not really.

Any suggestion will probably include macros. Macros can be disabled
and
your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

--

Dave Peterson

Sean, change Application.Quit to
ThisWorkbook.Close False
or if you want to get really fancy, this:
If Workbooks.Count = 1 Then
'Only this book is open go ahead and quit
Application.Quit
Else
'Must be something else open
ThisWorkbook.Close False
End If

Also as Dave has pointed out, have you experimented with what happens to
your workbook when you open it and select "Disable Macros"? I would either
change the file permissions from Windows or put the file in a password
protected zip file.

--
Charles Chickering

"A good example is twice the value of good advice."


"Sean" wrote:

Well I'm pretty pleased with the attached which will open at a Blank
sheet if its a non-permitted user, otherwise it opens on an info sheet.
One slight problem is that if its a non-permitted user the whole Excel
application closes, which I wouldn't want if other active files are
open. How would I just close the file I'm attempting to open?

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.GoTo Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Input").Select
Application.ScreenUpdating = True

Dim myArray As Variant
Dim arName As String


arName = "Users"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value


With Application
If IsError(.Match(.UserName, myArray, 0)) Then
ThisWorkbook.Sheets("Blank Sheet").Select

MsgBox "You are NOT Permitted to access this File " & vbNewLine & _
" " & vbNewLine & _
"Please Contact " & vbNewLine & _
" " & vbNewLine & _
"Joe Bloggs at " & vbNewLine & _
" " & vbNewLine & _
"ABC Group +0019 66200000"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
ThisWorkbook.Sheets("Input").Select

End Sub







Mike Fogleman wrote:

You're right Dave, the best we can do is keep honest people honest.

Mike F
"Dave Peterson" wrote in message
...
I don't think there is any workaround to the user disabling macros.

Once the workbook is opened, the user can show hidden sheets and unprotect
any
worksheet (or the workbook).

And if the user knows how to use google, the user can find a way to bypass
the
project protection.

If you really have sensitive data, don't put it into excel. If you have
to put
it into excel, don't share it with anyone you don't trust.

Mike Fogleman wrote:

Dave has a valid point. There is a work-around for those who disable
macros,
using a "Splash" sheet, if you're interested.

Mike F
"Dave Peterson" wrote in message
...
Not really.

Any suggestion will probably include macros. Macros can be disabled
and
your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

--

Dave Peterson

Thanks Charles

No I haven't, not really sure where the Disable Macro feature is


Charles Chickering wrote:

Sean, change Application.Quit to
ThisWorkbook.Close False
or if you want to get really fancy, this:
If Workbooks.Count = 1 Then
'Only this book is open go ahead and quit
Application.Quit
Else
'Must be something else open
ThisWorkbook.Close False
End If

Also as Dave has pointed out, have you experimented with what happens to
your workbook when you open it and select "Disable Macros"? I would either
change the file permissions from Windows or put the file in a password
protected zip file.

--
Charles Chickering

"A good example is twice the value of good advice."


"Sean" wrote:

Well I'm pretty pleased with the attached which will open at a Blank
sheet if its a non-permitted user, otherwise it opens on an info sheet.
One slight problem is that if its a non-permitted user the whole Excel
application closes, which I wouldn't want if other active files are
open. How would I just close the file I'm attempting to open?

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.GoTo Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Input").Select
Application.ScreenUpdating = True

Dim myArray As Variant
Dim arName As String


arName = "Users"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value


With Application
If IsError(.Match(.UserName, myArray, 0)) Then
ThisWorkbook.Sheets("Blank Sheet").Select

MsgBox "You are NOT Permitted to access this File " & vbNewLine & _
" " & vbNewLine & _
"Please Contact " & vbNewLine & _
" " & vbNewLine & _
"Joe Bloggs at " & vbNewLine & _
" " & vbNewLine & _
"ABC Group +0019 66200000"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
ThisWorkbook.Sheets("Input").Select

End Sub







Mike Fogleman wrote:

You're right Dave, the best we can do is keep honest people honest.

Mike F
"Dave Peterson" wrote in message
...
I don't think there is any workaround to the user disabling macros.

Once the workbook is opened, the user can show hidden sheets and unprotect
any
worksheet (or the workbook).

And if the user knows how to use google, the user can find a way to bypass
the
project protection.

If you really have sensitive data, don't put it into excel. If you have
to put
it into excel, don't share it with anyone you don't trust.

Mike Fogleman wrote:

Dave has a valid point. There is a work-around for those who disable
macros,
using a "Splash" sheet, if you're interested.

Mike F
"Dave Peterson" wrote in message
...
Not really.

Any suggestion will probably include macros. Macros can be disabled
and
your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

--

Dave Peterson

As Dave Peterson pointed out, Excel is an easy nut to crack for those who
really want to. Nothing is truly safe in Excel from those who persist. That
said, the theory behind the Splash sheet is to hide all worksheets except
the Splash, when the workbook closes. If the workbook is opened with macros
disabled, then the Splash sheet is the only one visible and the macro to
unhide the other sheets cannot run. Create a worksheet named "Splash" and if
you want, put "ACCESS DENIED!" in the middle with large font. Once that is
done use the following code in ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name = "Splash" Then
ws.Visible = True
Else
ws.Visible = False
End If
Next
End Sub

Modify the Workbook_Open event as follows:
MsgBox no longer needed.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String
Dim ws As Worksheet


arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.WorksheetFunction.Match(.UserName, myArray, 0)) Then
'MsgBox "You are NOT permitted to Open this File ! !"
Application.DisplayAlerts = False
ThisWorkbook.Close False
Else
For Each ws In Worksheets
ws.Visible = True
Next
Worksheets("Splash").Visible = False
Worksheets("Users").Visible = False
Worksheets("Sheet1").Activate
End If
End With
Application.DisplayAlerts = True
End Sub

Mike F
"Sean" wrote in message
oups.com...
Thanks Mike, it works great. How would I include that it opens on a
Blank Sheet, say Sheet1 if I get the message "You are not permitted"
etc and if you don't it goes to Sheet2 A1

Slight problem I see is that a non-permitted user may see info
displayed on the screen and it won't close until they click ok to the
message


Mike Fogleman wrote:

Right you are! Parentheses wrong order.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName, myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

Mike F
"Sean" wrote in message
oups.com...
Thanks Mike, I'm getting a Syntax error on line
If IsError(.Match(.UserName ), myArray, 0)) Then

Mike Fogleman wrote:

One way is to put the list of users in the workbook somewhere, ie, a
worksheet named "Users" The worksheet can be hidden from view but
easily
accessed to update the list without changing the code. Make the list a
Named
Range like "MyUsers" (not the same as a worksheet name). This list
will
be
added to an array and will be compared to the .UserName of the person
trying
to open the workbook.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName ), myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !",
xlExclamation
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

This is untested, so try it out or modify as needed.
Mike F
"Sean" wrote in message
oups.com...
Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within
the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Sean,
No,
The Username is the Name used to Open Excel, not the PC username.

To add MORE names use something like:


Private Sub Workbook_Open()
With Application
If .UserName = "Fred Flintstone" Or .UserName = "Wilma Flintstone" Then '
AND So on to include ALL excel usernames
Else
MsgBox "You are NOT permitted to Open this File ! !", xlExclamation
Application.DisplayAlerts = False
Application.Quit
End If
End With
End Sub


Corey....



"Sean" wrote in message
oups.com...
Thanks Corey that looks pretty good. How would I include Wilma in the
allowed list?

I assume the Username is the computers logged in User??


Corey wrote:

How about:

Private Sub Workbook_Open()
With Application
If .UserName = "Fred Flintstone" Then
Else
MsgBox "You are NOT permitted to Open this File ! !", xlExclamation
Application.DisplayAlerts = False
Application.Quit
End If
End With
End Sub


Corey....

"Sean" wrote in message
ups.com...
Can't use a password as the user who is allowed just e-mails it to
someone who shouldn't have it and just tells the password

Mike wrote:

why don't you use a password?

"Sean" wrote:

Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within
the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Tools/Macros/Security - set it to high or medium (medium will prompt you to
enable/disable macros).

Or, if opening the book from Windows Explorer, select the file, hold the
Shift key, then hit enter to open the file (while holding down the shift key).

Or, disable events prior to opening the workbook (average users may not know
or care how to do this - but I keep a toggle button on my toolbar so I can
open workbooks w/o triggering event handlers).

Or, change the excel username (Tools/Options/General) to the name of someone
who is approved to open the file.

Excel's protection is useful to keep people from inadvertently altering the
spreadsheet - but you shouldn't seriously think you are going to keep anyone
out.



"Sean" wrote:

Thanks Charles

No I haven't, not really sure where the Disable Macro feature is


Charles Chickering wrote:

Sean, change Application.Quit to
ThisWorkbook.Close False
or if you want to get really fancy, this:
If Workbooks.Count = 1 Then
'Only this book is open go ahead and quit
Application.Quit
Else
'Must be something else open
ThisWorkbook.Close False
End If

Also as Dave has pointed out, have you experimented with what happens to
your workbook when you open it and select "Disable Macros"? I would either
change the file permissions from Windows or put the file in a password
protected zip file.

--
Charles Chickering

"A good example is twice the value of good advice."


"Sean" wrote:

Well I'm pretty pleased with the attached which will open at a Blank
sheet if its a non-permitted user, otherwise it opens on an info sheet.
One slight problem is that if its a non-permitted user the whole Excel
application closes, which I wouldn't want if other active files are
open. How would I just close the file I'm attempting to open?

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.GoTo Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Input").Select
Application.ScreenUpdating = True

Dim myArray As Variant
Dim arName As String


arName = "Users"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value


With Application
If IsError(.Match(.UserName, myArray, 0)) Then
ThisWorkbook.Sheets("Blank Sheet").Select

MsgBox "You are NOT Permitted to access this File " & vbNewLine & _
" " & vbNewLine & _
"Please Contact " & vbNewLine & _
" " & vbNewLine & _
"Joe Bloggs at " & vbNewLine & _
" " & vbNewLine & _
"ABC Group +0019 66200000"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
ThisWorkbook.Sheets("Input").Select

End Sub







Mike Fogleman wrote:

You're right Dave, the best we can do is keep honest people honest.

Mike F
"Dave Peterson" wrote in message
...
I don't think there is any workaround to the user disabling macros.

Once the workbook is opened, the user can show hidden sheets and unprotect
any
worksheet (or the workbook).

And if the user knows how to use google, the user can find a way to bypass
the
project protection.

If you really have sensitive data, don't put it into excel. If you have
to put
it into excel, don't share it with anyone you don't trust.

Mike Fogleman wrote:

Dave has a valid point. There is a work-around for those who disable
macros,
using a "Splash" sheet, if you're interested.

Mike F
"Dave Peterson" wrote in message
...
Not really.

Any suggestion will probably include macros. Macros can be disabled
and
your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

--

Dave Peterson

Thanks everyone, it doesn't give me Fort Knox, but enough to frustrate


JMB wrote:

Tools/Macros/Security - set it to high or medium (medium will prompt you to
enable/disable macros).

Or, if opening the book from Windows Explorer, select the file, hold the
Shift key, then hit enter to open the file (while holding down the shift key).

Or, disable events prior to opening the workbook (average users may not know
or care how to do this - but I keep a toggle button on my toolbar so I can
open workbooks w/o triggering event handlers).

Or, change the excel username (Tools/Options/General) to the name of someone
who is approved to open the file.

Excel's protection is useful to keep people from inadvertently altering the
spreadsheet - but you shouldn't seriously think you are going to keep anyone
out.



"Sean" wrote:

Thanks Charles

No I haven't, not really sure where the Disable Macro feature is


Charles Chickering wrote:

Sean, change Application.Quit to
ThisWorkbook.Close False
or if you want to get really fancy, this:
If Workbooks.Count = 1 Then
'Only this book is open go ahead and quit
Application.Quit
Else
'Must be something else open
ThisWorkbook.Close False
End If

Also as Dave has pointed out, have you experimented with what happens to
your workbook when you open it and select "Disable Macros"? I would either
change the file permissions from Windows or put the file in a password
protected zip file.

--
Charles Chickering

"A good example is twice the value of good advice."


"Sean" wrote:

Well I'm pretty pleased with the attached which will open at a Blank
sheet if its a non-permitted user, otherwise it opens on an info sheet.
One slight problem is that if its a non-permitted user the whole Excel
application closes, which I wouldn't want if other active files are
open. How would I just close the file I'm attempting to open?

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.GoTo Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Input").Select
Application.ScreenUpdating = True

Dim myArray As Variant
Dim arName As String


arName = "Users"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value


With Application
If IsError(.Match(.UserName, myArray, 0)) Then
ThisWorkbook.Sheets("Blank Sheet").Select

MsgBox "You are NOT Permitted to access this File " & vbNewLine & _
" " & vbNewLine & _
"Please Contact " & vbNewLine & _
" " & vbNewLine & _
"Joe Bloggs at " & vbNewLine & _
" " & vbNewLine & _
"ABC Group +0019 66200000"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
ThisWorkbook.Sheets("Input").Select

End Sub







Mike Fogleman wrote:

You're right Dave, the best we can do is keep honest people honest.

Mike F
"Dave Peterson" wrote in message
...
I don't think there is any workaround to the user disabling macros.

Once the workbook is opened, the user can show hidden sheets and unprotect
any
worksheet (or the workbook).

And if the user knows how to use google, the user can find a way to bypass
the
project protection.

If you really have sensitive data, don't put it into excel. If you have
to put
it into excel, don't share it with anyone you don't trust.

Mike Fogleman wrote:

Dave has a valid point. There is a work-around for those who disable
macros,
using a "Splash" sheet, if you're interested.

Mike F
"Dave Peterson" wrote in message
...
Not really.

Any suggestion will probably include macros. Macros can be disabled
and
your
information would be available to anyone who had the file.



Sean wrote:

Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

--

Dave Peterson

--

Dave Peterson

Mike I took your Splash screen on board, I've got it to Hide all sheets
etc, but now my Message pop up window doesn't appear and neither does
my info page when its a permitted user. What have I done wrong? Both
codes below are in ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
For Each ws In Worksheets
If ws.Name = "Splash" Then
ws.Visible = True
Else
ws.Visible = False
End If
Next

End Sub

Private Sub Workbook_Open()
Dim sh As Worksheet
With Application
If .UserName = "John Doe" Or .UserName = "Joe Bloggs" Then
ThisWorkbook.Sheets("info").Visible
ThisWorkbook.Sheets("info").Select
Else
ThisWorkbook.Sheets("Splash").Select
MsgBox "You are NOT Permitted to access this File " & vbCr & _
"" & vbCr & _
"Please Contact Joe Bloggs at " & vbCr & _
"" & vbCr & _
"ABC Group +09992 1 25480000"
ThisWorkbook.Sheets("info").Select
Application.DisplayAlerts = False
ThisWorkbook.Close False
End If
End With

End Sub











Mike Fogleman wrote:

As Dave Peterson pointed out, Excel is an easy nut to crack for those who
really want to. Nothing is truly safe in Excel from those who persist. That
said, the theory behind the Splash sheet is to hide all worksheets except
the Splash, when the workbook closes. If the workbook is opened with macros
disabled, then the Splash sheet is the only one visible and the macro to
unhide the other sheets cannot run. Create a worksheet named "Splash" and if
you want, put "ACCESS DENIED!" in the middle with large font. Once that is
done use the following code in ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name = "Splash" Then
ws.Visible = True
Else
ws.Visible = False
End If
Next
End Sub

Modify the Workbook_Open event as follows:
MsgBox no longer needed.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String
Dim ws As Worksheet


arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.WorksheetFunction.Match(.UserName, myArray, 0)) Then
'MsgBox "You are NOT permitted to Open this File ! !"
Application.DisplayAlerts = False
ThisWorkbook.Close False
Else
For Each ws In Worksheets
ws.Visible = True
Next
Worksheets("Splash").Visible = False
Worksheets("Users").Visible = False
Worksheets("Sheet1").Activate
End If
End With
Application.DisplayAlerts = True
End Sub

Mike F
"Sean" wrote in message
oups.com...
Thanks Mike, it works great. How would I include that it opens on a
Blank Sheet, say Sheet1 if I get the message "You are not permitted"
etc and if you don't it goes to Sheet2 A1

Slight problem I see is that a non-permitted user may see info
displayed on the screen and it won't close until they click ok to the
message


Mike Fogleman wrote:

Right you are! Parentheses wrong order.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName, myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

Mike F
"Sean" wrote in message
oups.com...
Thanks Mike, I'm getting a Syntax error on line
If IsError(.Match(.UserName ), myArray, 0)) Then

Mike Fogleman wrote:

One way is to put the list of users in the workbook somewhere, ie, a
worksheet named "Users" The worksheet can be hidden from view but
easily
accessed to update the list without changing the code. Make the list a
Named
Range like "MyUsers" (not the same as a worksheet name). This list
will
be
added to an array and will be compared to the .UserName of the person
trying
to open the workbook.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName ), myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !",
xlExclamation
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

This is untested, so try it out or modify as needed.
Mike F
"Sean" wrote in message
oups.com...
Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within
the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks

Worked why my message pop up was not appearing, I had turned off
macro's!!!!!!



Sean wrote:

Mike I took your Splash screen on board, I've got it to Hide all sheets
etc, but now my Message pop up window doesn't appear and neither does
my info page when its a permitted user. What have I done wrong? Both
codes below are in ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
For Each ws In Worksheets
If ws.Name = "Splash" Then
ws.Visible = True
Else
ws.Visible = False
End If
Next

End Sub

Private Sub Workbook_Open()
Dim sh As Worksheet
With Application
If .UserName = "John Doe" Or .UserName = "Joe Bloggs" Then
ThisWorkbook.Sheets("info").Visible
ThisWorkbook.Sheets("info").Select
Else
ThisWorkbook.Sheets("Splash").Select
MsgBox "You are NOT Permitted to access this File " & vbCr & _
"" & vbCr & _
"Please Contact Joe Bloggs at " & vbCr & _
"" & vbCr & _
"ABC Group +09992 1 25480000"
ThisWorkbook.Sheets("info").Select
Application.DisplayAlerts = False
ThisWorkbook.Close False
End If
End With

End Sub











Mike Fogleman wrote:

As Dave Peterson pointed out, Excel is an easy nut to crack for those who
really want to. Nothing is truly safe in Excel from those who persist. That
said, the theory behind the Splash sheet is to hide all worksheets except
the Splash, when the workbook closes. If the workbook is opened with macros
disabled, then the Splash sheet is the only one visible and the macro to
unhide the other sheets cannot run. Create a worksheet named "Splash" and if
you want, put "ACCESS DENIED!" in the middle with large font. Once that is
done use the following code in ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name = "Splash" Then
ws.Visible = True
Else
ws.Visible = False
End If
Next
End Sub

Modify the Workbook_Open event as follows:
MsgBox no longer needed.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String
Dim ws As Worksheet


arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.WorksheetFunction.Match(.UserName, myArray, 0)) Then
'MsgBox "You are NOT permitted to Open this File ! !"
Application.DisplayAlerts = False
ThisWorkbook.Close False
Else
For Each ws In Worksheets
ws.Visible = True
Next
Worksheets("Splash").Visible = False
Worksheets("Users").Visible = False
Worksheets("Sheet1").Activate
End If
End With
Application.DisplayAlerts = True
End Sub

Mike F
"Sean" wrote in message
oups.com...
Thanks Mike, it works great. How would I include that it opens on a
Blank Sheet, say Sheet1 if I get the message "You are not permitted"
etc and if you don't it goes to Sheet2 A1

Slight problem I see is that a non-permitted user may see info
displayed on the screen and it won't close until they click ok to the
message


Mike Fogleman wrote:

Right you are! Parentheses wrong order.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName, myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !"
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

Mike F
"Sean" wrote in message
oups.com...
Thanks Mike, I'm getting a Syntax error on line
If IsError(.Match(.UserName ), myArray, 0)) Then

Mike Fogleman wrote:

One way is to put the list of users in the workbook somewhere, ie, a
worksheet named "Users" The worksheet can be hidden from view but
easily
accessed to update the list without changing the code. Make the list a
Named
Range like "MyUsers" (not the same as a worksheet name). This list
will
be
added to an array and will be compared to the .UserName of the person
trying
to open the workbook.

Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String

arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value

With Application
If IsError(.Match(.UserName ), myArray, 0)) Then
MsgBox "You are NOT permitted to Open this File ! !",
xlExclamation
Application.DisplayAlerts = False
Application.Quit
Else
End If
End With
End Sub

This is untested, so try it out or modify as needed.
Mike F
"Sean" wrote in message
oups.com...
Is it possible to deny access to open an Excel file except to
specified
user?

I assume this code as well as the user access list would go within
the
ThisWorksheet. In that on opening the file, it would compare the
Computer user logged in to the list and if "Joe Bloggs" or "Joe
Public"
is listed it would allow it to open, otherwise a message "Go Away"
would appear and file would not open

Is this possible, or is it way too advanced?

Thanks