Access Control With UserName
 

Thought I could do this, but not experienced enough, would appreciate
some help. I want a Shared WB: a record of all users’ in & out times,
flexitime balance, leave taken, etc, which they will update themselves
(have already set an individual WS up to do this, but want to expand
WB so that each user will have their own WS in the WB.) I want WB to
react to username (Windows login name). WS ‘control’ is for admin
use, it has list of mgr usernames in Col C, list of admin usernames in
Col E and admin pw in G5, WS: ‘blank’ is a WS with some text &
formulae in it but no user records. Both these WSs are hidden. All
other WSs are named after the usernames of the users who are already
set up. On Open, I want...

If username isn’t found as any WS name or in ‘control’ WS Col C
(mgrs), disallow open. If username is found as any WS name (ie user
is already set up with own WS), activate that user's WS & disallow any
other WS activation, otherwise assume user is mgr or admin'r and allow
view-only-no-chages of any WS. Allow any admin’r listed in ‘control’
WS Col E to call up Input Box to input admin pw; if correct, allow
anything. WSs will increase in number as admin’r creates them by
pasting from WS ‘blank’.

Access Control With UserName
 

here's some to get you going:

Option Explicit
Sub checksheets()
Dim bfound As Boolean
Dim ws As Worksheet
Dim username As String
'get system user name
username = Environ$("username")
'check worksheets
For Each ws In Worksheets
If ws.Name = username Then
bfound = True
Exit For
End If
Next
If Not bfound Then
'check if manager
If IsManager(username) Then
For Each ws In Worksheets
ws.Visible = xlSheetVisible
Next
End If
End If

End Sub
Function IsManager(sName As String) As Boolean
On Error Resume Next
Dim index As Long
index = _
WorksheetFunction.Match(sName, Worksheets("control").Range("c1:C100"), False)
IsManager = index < 0
End Function


"robzrob" wrote:

Thought I could do this, but not experienced enough, would appreciate
some help. I want a Shared WB: a record of all users in & out times,
flexitime balance, leave taken, etc, which they will update themselves
(have already set an individual WS up to do this, but want to expand
WB so that each user will have their own WS in the WB.) I want WB to
react to username (Windows login name). WS €˜control is for admin
use, it has list of mgr usernames in Col C, list of admin usernames in
Col E and admin pw in G5, WS: €˜blank is a WS with some text &
formulae in it but no user records. Both these WSs are hidden. All
other WSs are named after the usernames of the users who are already
set up. On Open, I want...

If username isnt found as any WS name or in €˜control WS Col C
(mgrs), disallow open. If username is found as any WS name (ie user
is already set up with own WS), activate that user's WS & disallow any
other WS activation, otherwise assume user is mgr or admin'r and allow
view-only-no-chages of any WS. Allow any adminr listed in €˜control
WS Col E to call up Input Box to input admin pw; if correct, allow
anything. WSs will increase in number as adminr creates them by
pasting from WS €˜blank.

Access Control With UserName
 

On Sep 1, 2:22*pm, Patrick Molloy
wrote:
here's some to get you going:

Option Explicit
Sub checksheets()
* *Dim bfound As Boolean
* *Dim ws As Worksheet
* *Dim username As String
* * * 'get system user name
* *username = Environ$("username")
* * * 'check worksheets
* *For Each ws In Worksheets
* * * * If ws.Name = username Then
* * * * * * bfound = True
* * * * * * Exit For
* * * *End If
* *Next
* *If Not bfound Then
* * * *'check if manager
* * * *If IsManager(username) Then
* * * * * *For Each ws In Worksheets
* * * * * * * ws.Visible = xlSheetVisible
* * * * * *Next
* * * *End If
* *End If

End Sub
Function IsManager(sName As String) As Boolean
*On Error Resume Next
*Dim index As Long
*index = _
WorksheetFunction.Match(sName, Worksheets("control").Range("c1:C100"), False)
IsManager = index < 0
End Function



"robzrob" wrote:
Thought I could do this, but not experienced enough, would appreciate
some help. *I want a Shared WB: a record of all users’ in & out times,
flexitime balance, leave taken, etc, which they will update themselves
(have already set an individual WS up to do this, but want to expand
WB so that each user will have their own WS in the WB.) *I want WB to
react to username (Windows login name). *WS ‘control’ is for admin
use, it has list of mgr usernames in Col C, list of admin usernames in
Col E and admin pw in G5, WS: ‘blank’ is a WS with some text &
formulae in it but no user records. *Both these WSs are hidden. *All
other WSs are named after the usernames of the users who are already
set up. *On Open, I want...

If username isn’t found as any WS name or in ‘control’ WS Col C
(mgrs), disallow open. *If username is found as any WS name (ie user
is already set up with own WS), activate that user's WS & disallow any
other WS activation, otherwise assume user is mgr or admin'r and allow
view-only-no-chages of any WS. *Allow any admin’r listed in ‘control’
WS Col E to call up Input Box to input admin pw; if correct, allow
anything. *WSs will increase in number as admin’r creates them by
pasting from WS ‘blank’.- Hide quoted text -

- Show quoted text -

Thanks - will give it a try. (Sorry for reply-delay, had no answers
for a few days so thought no-one interested.)