Access Control With UserName

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

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’.
 
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
 
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






- Show quoted text -
Click to expand...

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