Lists of authorised users

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
Afternoon all

I have a spreadsheet that is open to all users but certain "superusers" have additional tabs available to them
I'm trying to write a peice of code triggered by the auto_open event but it seems a bit long-winded. I'm using a variation of an "If" statement which looks like this :-
Code:
If application.username="username goes here" or application.username="username goes here" then
    Sheets("4-weekly ave").Visible = True
    Sheets("weekly stats").Visible = True
endif

As the list of users is growing, I get the feeling there is a better way of checking if the user is a "superuser" rather than enlarging the IF statement above. For a normal user, the two sheets named above are "veryhidden" so they can't go into Format/sheets/Unhide.

Any ideas will be greatly appreciated.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
What I do in cases like this is to place a named range in a Very Hidden sheet that contains the super users. When you want to edit the list of suerusers, you simply insert of remove cells/names in the list.

Then I have code that looks like this:

Code:
dim flag as boolean
dim item as range

flag = false

for each item in range("Superusers")

  if item.value = Environ("username") then

    flag = true

    exit for

  end if

next item

If flag then 
    Sheets("4-weekly ave").Visible = xlsheetvisible 
    Sheets("weekly stats").Visible = xlsheetvisible

else

    Sheets("4-weekly ave").Visible = xlsheetveryhidden 
    Sheets("weekly stats").Visible = xlsheetveryhidden

endif
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
Thanks hatman

Had to modify it slightly for two different types of groups but it's now working fine!

(y)
 

Forum statistics

Threads
1,141,625
Messages
5,707,476
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top