rights management

[mole999]

I'd like to hide / unhide specific sheets, if the user is known to the workbook

its normally on a secure network, but the book would be copied between departments

the code is as secure as i can make it, so i can hard code the names inside the code

senario

userA has all rights, as workbook opens checks against network ID and ensures sheets are unhidden

userB doesnt have all rights, so when the book open, we make the sheets xlhidden

unknown user, close further pages

any clues where best to start or some draft code that can be adjusted

 

[Simon Lloyd]

Take a look here http://www.thecodecage.com/forumz/m...s/167802-hide-lock-worksheets.html#post605112 it's a worked example by Gordon Dibben

 

[Taul]

also here
http://www.mrexcel.com/forum/showthread.php?t=530142

 

[mole999]

Thanx for the pointers peeps

For comment, I don't need it more complex than that, any simple shortening of code ?

Sub openup()
On Error Resume Next
Dim UserA As String
Dim UserB As String
Dim UserC As String
Dim UserD As String
Dim UserE As String
Dim UserF As String
Dim UserG As String
Dim UserH As String
Dim UserI As String
Dim UserJ As String


UserA = "mole"
UserB = ""
UserC = ""
UserD = ""
UserE = ""
UserF = ""
UserG = ""
UserH = ""
UserI = ""
UserJ = ""

If Environ("username") = UserA Or Environ("username") = UserB Or Environ("username") = UserC Or Environ("username") = UserD _
Or Environ("username") = UserE Or Environ("username") = UserF Or Environ("username") = UserG Or Environ("username") = UserH _
Or Environ("username") = UserI Or Environ("username") = UserJ Or Environ("username") = UserK Or Environ("username") = UserL _
Or Environ("username") = UserM Or Environ("username") = UserN Or Environ("username") = UserO Or Environ("username") = UserP _
Or Environ("username") = UserQ Or Environ("username") = UserR Or Environ("username") = UserS Or Environ("username") = UserT _
Or Environ("username") = UserU Or Environ("username") = UserV Or Environ("username") = UserW Or Environ("username") = UserX _
Or Environ("username") = UserY Or Environ("username") = UserZ Then

'Authorised
Sheets("1").Visible = xlVeryHidden
Sheets("2").Visible = True
Exit Sub
Else

'Not Authorsied
Sheets("1").Visible = True
Sheets("2").Visible = xlVeryHidden
Exit Sub
End If

End Sub

 

[Ruddles]

Sheets("1").Visible = xlVeryHidden
Sheets("2").Visible = True

If they're the only two sheets, you'll need to swap those two statements around.

 

[shg]

If you have three worksheets with codenames wksSplash, wksConf1, and wksConf2, and the workbook is always saved with only the splash sheet visible, then

Sub OpenUp()
    Dim avsAll      As Variant
    Dim avsSome     As Variant
    Dim sUser       As String
 
    avsAll = Array("Alan", "Barb", "Chuck")
    avsSome = Array("Dana", "Ed", "Fran", "George")
    sUser = Environ("username")
 
    If IsNumeric(Application.Match(sUser, avsAll, 0)) Then
        wksConf1.Visible = xlSheetVisible
        wksConf2.Visible = xlSheetVisible
 
        wksSplash.Visible = xlVeryHidden
 
    ElseIf IsNumeric(Application.Match(sUser, avsSome, 0)) Then
        wksConf1.Visible = xlSheetVisible
        wksConf2.Visible = xlSheetVeryHidden
 
        wksSplash.Visible = xlVeryHidden
    End If
End Sub

 

[Ruddles]

Sub openup()
On Error Resume Next

Why the ON ERROR?

Maybe use SELECT:-

Select case Environ("username")
  Case UserA, UserB, UserC, UserD, UserE, UserF, UserG, UserH, UserI ' etc
    'Authorised
    Sheets("2").Visible = True
    Sheets("1").Visible = xlVeryHidden
  Case Else
    'Not Authorsied
    Sheets("1").Visible = True
    Sheets("2").Visible = xlVeryHidden
End Select

Although I might do something like this:-

option explicit
option compare text
 
sub openup()
 
dim vauth as variant
dim i as integer
 
[COLOR=green]'Assume Not Authorsied[/COLOR]
Sheets("1").Visible = True
Sheets("2").Visible = xlVeryHidden
 
vAuth=array("mole","fred","some1else","etc")
 
for i=lbound(vAuth) to ubound(vAuth)
  if environ("username")=vAuth(i) then
    [COLOR=green]'Deffo Authorised[/COLOR]
    Sheets("2").Visible = True
    Sheets("1").Visible = xlVeryHidden
    exit sub [COLOR=green]' if you don't intend to do anything else in this sub[/COLOR]
  endif
next i
 
end sub

 

[mole999]

If they're the only two sheets, you'll need to swap those two statements around.

Very True, I hadn't thought along those lines, though the file will have maybe five sheets always visible

 

[mole999]

If you have three worksheets with codenames wksSplash, wksConf1, and wksConf2, and the workbook is always saved with only the splash sheet visible, then

Sub OpenUp()
    Dim avsAll      As Variant
    Dim avsSome     As Variant
    Dim sUser       As String
 
    avsAll = Array("Alan", "Barb", "Chuck")
    avsSome = Array("Dana", "Ed", "Fran", "George")
    sUser = Environ("username")
 
    If IsNumeric(Application.Match(sUser, avsAll, 0)) Then
        wksConf1.Visible = xlSheetVisible
        wksConf2.Visible = xlSheetVisible
 
        wksSplash.Visible = xlVeryHidden
 
    ElseIf IsNumeric(Application.Match(sUser, avsSome, 0)) Then
        wksConf1.Visible = xlSheetVisible
        wksConf2.Visible = xlSheetVeryHidden
 
        wksSplash.Visible = xlVeryHidden
    End If
End Sub

that looks better, though unless named they other users will have partial access (well that's the intention at this stage)

I won't be restricting to a opening page

 

[shg]

I won't be restricting to a opening page

If you don't, and users disable macros, they will see whatever sheets were visible when the workbook was last saved.

EDIT:

        wksConf2.Visible = xl[COLOR=red]Sheet[/COLOR]VeryHidden
 
        wksSplash.Visible = xlVeryHidden

That was sloppy, sorry; they both work, but the enumeration names should be consistent.