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

 

[mole999]

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

ON ERROR, because I always miss something, I take them out when I'm happy its stable.
I like this, good to test it in my work environment, then build into the workbook

 

[Simon Lloyd]

Why hard code the names, it makes it a pain to manage if you have quiet a few users, a lookup sheet (xlveryhidden) and VBA find is just as quick and a bit easier to manage, in fact you could then check the offset for a flag of your choice to allow/disallow whatever you want.

 

[mole999]

eight users, one department, same office to manage with full rights, and all others won't have full access, its not too onerous

 

[mole999]

Its been a good few days in test and appears very stable

one possible issue

    who = Environ("username")

    vauth = Array("mole","fred","some1else","etc")

    For i = LBound(vauth) To UBound(vauth)
        If Environ("username") = vauth(i) Then

is there any way to loose what appears to be case sensitivity on the user name (environ) I used all the staffs email addresses, but one or two just don't seem to work

 

[shg]

who = lcase(Environ("username"))

 

[mole999]

thanks, hadn't even thought like that