Show sheets with same Code Name

[SIGE]

Hi,

I have "renamed" the sheets in my Wb to something "logic":

Sheet1 to Sheet4 became ADMIN1 to ADMIN4
Sheet5 to Sheet7 became PRICE1 to PRICE3
etc

In an Administration sheet I would keep the users with the "access-level" to their respective sheets.
Eg. Mark - ADMIN
John - ADMIN
Claire - PRICE
...

Would it be possible to run a routine that unhides all ADMIN sheets when I am Mark? OR only the PRICE sheets when I am Claire.
Something like:
select case left(lcase(ws.codename),5)
case is = "admin" then unhdide ADMIN-sheets.

Hope this makes any sense to someone!
Help greatly appreciated,
Sige

 

[lenze]

Try something like this in the ThisWorkbook module!!

Private Sub Workbook_Open()
MsgBox "OK"
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = xlSheetVisible
Next ws
Select Case Environ("UserName")
Case Is = "Mark", "John", "Mary"
    Sheets("Admin1").Visible = xlSheetHidden
    Sheets("Admin2").Visible = xlSheetHidden
    Sheets("Admin3").Visible = xlSheetHidden
    Sheets("Admin4").Visible = xlSheetHidden
Case Else:
End Select
End Sub

lenze

 

[SIGE]

Hi Lenze,

Thanks for your assistance, though i was hoping for a routine that would be "neater" for my purposes, as I had simplified my example a little, things might have been less clear, so I try again^^ Hope you don't mind.

I will have a list of "50" users (say column A) on my "User-sheet" ... which each belong to a category (say column B): ADMIN, PRICE, LOGIS, PURCH, CALCS, TECHN, VERVE, CORTE, KRUUG, TREED, BIIFS, and still some other ...

I have changed the Codename of the Sheets, not the Tab name ... so each Sheet starts with a certain Category... there could be 1 sheet belonging to 1 Category, but also 4 or 7 or 10, or ...

So depending on what "User" is selected ... he will correspond to a specific Category as specified on my UserSheet. When found the proper Category the routine should show all Sheets whose Codename starts with the Category that belong to the selected user.
Eg. Show sheets with Codename (not Tab sheet name" ...as maybe users will change the name) LOGIS1, LOGIS2, LOGIS3, ..LOGIS37 ..if the selected User is "Jeff" and he belongs to the Category LOGIS... and hide all the other sheets.

Hope you can help me on the way!!
Sige

 

[lenze]

Does a user belong to more than ONE group??

lenze

 

[SIGE]

Hi Lenze,

Ideally a user could belong to more than 1 group, where the Username goes in column A, and the categories he/she belongs to go into columns B,C,D,E,..
eg

Jeff-LOGIS-PRICE-TECH
Maria-ADMIN
...

But this could be really complicated?

It would be really incredibly awesome though, as I would not have to "duplicate" some of the data (sheets).

Wondering-where-Excel-possibilities-end, Sige

 

[RSXchin]

You can give only users with a certain username access to the first few sheets if you want.

 

[SIGE]

Hi Rxschin,
That's indeed what I try to establish ... Hence my request^^
Determining the functionality I am good at, getting the routine to work is another matter.
Sige

 

[RSXchin]

try something like this. Be sure to have their EXACT windows logon.

Private Sub Workbook_Open()
    Select Case Application.username
        Case "Jerry", "Mary"
            Sheets(1).Hide
            Sheets(2).Hide
            Sheets(3).Hide
    End Select
End Sub

 

[Norie]

Sige

Would it not be simpler, and perhaps even securer (think that's a word!?!), to have separate workbooks?

 

[SIGE]

Hi Norie,
I will/do distribute seperate workbooks already according to sensitivity of the data. Though it are some "work sheets" where it is best to keep the data in a single wb ...

Rsxchin,
the "username" issue is not what I am trying to resolve here^^


Sige