rights management

mole999 · Mar 6, 2011

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 · Mar 6, 2011

Ruddles said:

Why the ON ERROR?

Maybe use SELECT:-

Rich (BB code):

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:-

Code:

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 · Mar 6, 2011

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 · Mar 6, 2011

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

mole999 · Mar 19, 2011

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

one possible issue

Code:

    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 · Mar 19, 2011

Code:

who = lcase(Environ("username"))

mole999 · Mar 19, 2011

thanks, hadn't even thought like that

rights management

mole999

Well-known Member

mole999

Well-known Member

Excel Facts

Simon Lloyd

Well-known Member

mole999

Well-known Member

mole999

Well-known Member

shg

MrExcel MVP

mole999

Well-known Member

Similar threads

Forum statistics

Share this page

rights management

mole999

Well-known Member

mole999

Well-known Member

Excel Facts

Simon Lloyd

Well-known Member

mole999

Well-known Member

mole999

Well-known Member

shg

MrExcel MVP

mole999

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock