I have a workbook that has 10 different sheets with names ranging from
"Finance" to "Operations." What I would like to do is have the application
review the username upon opening and then run it against a list to first see
if that username is on the list. If it isn't, then nothing will open.
Secondly if it is on the list, I would like it to compare accesses to the
sheets, i.e.:

dsmith works in Operations
brogers works in Finance
mwilson works in both Operations and Finance

Dsmith should be able to view the "operations" sheet and nothing more, just
as brogers should view "finance" and nothing more. However, mwilson should
be able to view both the "operations" and "finance" sheets, but none of the
other 8 remaining sheets.

I am not sure where to even begin.

If the user disabled macros when they opened the workbook, then they will
have the ability to access any sheet they want. So if you allow them to
open the workbook, then assume they will be able to access anything in the
workbook.

--
Regards,
Tom Ogilvy



"DaS" wrote:

I have a workbook that has 10 different sheets with names ranging from
"Finance" to "Operations." What I would like to do is have the application
review the username upon opening and then run it against a list to first see
if that username is on the list. If it isn't, then nothing will open.
Secondly if it is on the list, I would like it to compare accesses to the
sheets, i.e.:

dsmith works in Operations
brogers works in Finance
mwilson works in both Operations and Finance

Dsmith should be able to view the "operations" sheet and nothing more, just
as brogers should view "finance" and nothing more. However, mwilson should
be able to view both the "operations" and "finance" sheets, but none of the
other 8 remaining sheets.

I am not sure where to even begin.

The way the workbook is setup is that all sheets are initially hidden, so if
macros are disabled it opens to what looks like a blank sheet. Macros
enabled makes the screens visible as you click through the userform that only
pops up when they are enabled. Most of my end users have no idea how to
navigate through excel in the slightest bit, so I'm not too worried about
that one in a very few that knows the tricks. Does that make sense Tom?

"Tom Ogilvy" wrote:

If the user disabled macros when they opened the workbook, then they will
have the ability to access any sheet they want. So if you allow them to
open the workbook, then assume they will be able to access anything in the
workbook.

--
Regards,
Tom Ogilvy



"DaS" wrote:

I have a workbook that has 10 different sheets with names ranging from
"Finance" to "Operations." What I would like to do is have the application
review the username upon opening and then run it against a list to first see
if that username is on the list. If it isn't, then nothing will open.
Secondly if it is on the list, I would like it to compare accesses to the
sheets, i.e.:

dsmith works in Operations
brogers works in Finance
mwilson works in both Operations and Finance

Dsmith should be able to view the "operations" sheet and nothing more, just
as brogers should view "finance" and nothing more. However, mwilson should
be able to view both the "operations" and "finance" sheets, but none of the
other 8 remaining sheets.

I am not sure where to even begin.

several approaches:

First two are demo'd from the immediate window:
sstr = environ("Username")
? sStr
OgilvyTW

sstr = environ("Computername")
? sStr
OLGILTG4

---------------------------------------

http://vbnet.mvps.org/index.html?cod...sergetinfo.htm

---------------------------------------

Michel Pierron - Gets the Fullname
Sub GetFullName()
Dim Domain$, User$
With CreateObject("Wscript.Network")
Domain = .UserDomain: User = .UserName
End With
MsgBox GetObject("WinNT://" & Domain & "/" & User & ",user").FullName, 64
End Sub

----------------------------------

From Google Search:
http://groups.google.com/advanced_group_search
=========
From: Trevor Shuttleworth )
Subject: Code to show login name
Newsgroups: microsoft.public.excel.programming
Date: 2001-01-16 12:54:01 PST




Private Declare Function apiGetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long

Sub GetUserNameTest()
MsgBox fOSUserName
End Sub

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX < 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

Regards

Trevor

then it is a matter of looping through the list of usernames and unhiding
the appropriate sheets when the username is found.
--
Regards,
Tom Ogilvy




"DaS" wrote:

The way the workbook is setup is that all sheets are initially hidden, so if
macros are disabled it opens to what looks like a blank sheet. Macros
enabled makes the screens visible as you click through the userform that only
pops up when they are enabled. Most of my end users have no idea how to
navigate through excel in the slightest bit, so I'm not too worried about
that one in a very few that knows the tricks. Does that make sense Tom?

"Tom Ogilvy" wrote:

If the user disabled macros when they opened the workbook, then they will
have the ability to access any sheet they want. So if you allow them to
open the workbook, then assume they will be able to access anything in the
workbook.

--
Regards,
Tom Ogilvy



"DaS" wrote:

I have a workbook that has 10 different sheets with names ranging from
"Finance" to "Operations." What I would like to do is have the application
review the username upon opening and then run it against a list to first see
if that username is on the list. If it isn't, then nothing will open.
Secondly if it is on the list, I would like it to compare accesses to the
sheets, i.e.:

dsmith works in Operations
brogers works in Finance
mwilson works in both Operations and Finance

Dsmith should be able to view the "operations" sheet and nothing more, just
as brogers should view "finance" and nothing more. However, mwilson should
be able to view both the "operations" and "finance" sheets, but none of the
other 8 remaining sheets.

I am not sure where to even begin.