Give Access to Certain Worksheets

[cyliyu]

I created a Main Menu to ease users to access to a particular worksheet when the correct button is clicked.
But I would like to control not to allow them to access all the worksheets.
For e.g. group A will have access to SheetA & AAList but no access to AChart sheet.

Any help, please?

 

[vletm]

cyliyu
Where could find 'group A's or other groups
lists of usernames and
where could those groups access?

 

[cyliyu]

I have created the groups and users access right in sheet 1.
Not sure is this the best way to define the access right.
Open for suggestion.

 

[vletm]

cyliyu
Note: I asked usernames which Excel knows/uses,
not names which 'parents' has given!
Ex if Your username is on that list, then You could see something ...

 

[AlanSidman]

If you don't know the username for each of your participants, then run this short macro on each user's PC and it will tell you the Windows UserName.

Sub FindUser()
Dim User As String
User = Environ("UserName")
MsgBox (User)
End Sub

 

[cyliyu]

cyliyu
Note: I asked usernames which Excel knows/uses,
not names which 'parents' has given!
Ex if Your username is on that list, then You could see something ...

Ok, The usernames was updated.
It consists of 3 groups. Thanks.

 

[cyliyu]

Ok, The usernames was updated.
It consists of 3 groups. Thanks.

Attached the wrong file. This should be the right one.

I read your code and understand as long as the user's name (network-Login ID) are input into column C in "GRPS" sheet, the users access right works.
How do I control which worksheet they have the access?

 

[vletm]

How do I control which worksheet they have the access?
If there is something (ex 'X' in cell) then
that user has access to sheet which has named in 'header'.
Ex the 1st name has access to 'SheetN', 'SheetP', 'Achart', 'Cchart'.
No matter of 'groups', just UserNames (C-column)matters!

NOTE!
if You'll change layout of that 'GRPS'-sheet,
then You have to change code too!
... or that code won't work .. at all!

btw ... interesting UserNames, but of course those are possible too!
Have You tested what would be Yours with 'Sub TextBox_Click'?

 

[cyliyu]

I got the "approved" users work great in your last attached file but not the access to a particular sheet.

Even remove all the "X" in the GRPS cells, the approved users still can access all the worksheets.

May I know what does .range("9:9") means in this statement?

xUN = WorksheetFunction.Match(shtname, Sheets("GRPS").Range("9:9"), 0)

 

[vletm]

.Range("9:9") is Your 'Sheet-name row' (before it was 4, now it's 9) - Okay?
With yUN- and xUN-values, it checks if that user/sheet -combination marked.
If marked then user can see that sheet.
If You have taken away ALL "X" ... hmm
... then even famous ANYBODY cannot see anything!
... if You add "X" in every cell, then EVERYBODY can see everything!
> I added one more sub for getting those usernames.

 

[cyliyu]

.Range("9:9") is Your 'Sheet-name row' (before it was 4, now it's 9) - Okay?

Yes, I noticed you have changed from 4 to 9. just like the C:C column to search for the users.

I got both the MsgBox "You Selected An Unknown Sheet!" & "You Are Unknown User!" work great but not the MsgBox "You Don't Have Permit To See That Selection"

I screenshot my testing results as follow:-
1) GRPS Settings for myself, only allow to access Dashboard, SheetA and SheetC


2) But when I click on the Partlists or SheetB, I am still allow to access this 2 sheets.

 

[vletm]

Are those two sheet 'hidden' before You start to clicking?
In the beginning ALL sheets have to be 'hidden' or more!
not this way!

and are those cells REALLY EMPTY ( even space is too much! )

 

[cyliyu]

Yes, I have enabled back the code and left only the "Home" and "GRPS" sheets.
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If (Sh.CodeName <> "Sheet1") And (Sh.CodeName <> "Sheet13") Then
Sh.Visible = xlSheetVeryHidden
End If
End Sub

I also make sure the cells really empty, and even tried to change the format to "Text" or "General" etc. but just couldn't get it work.

 

[vletm]

Ouch! - Now I got it!
One 's' missed - sorry!
... and of course that 'GRPS' should be hidden too.

 

[cyliyu]

Ouch! - Now I got it!
One 's' missed - sorry!
... and of course that 'GRPS' should be hidden too.

It work now!
didn't notice a 's' was missing in '.Cells'.

Thanks and appreciate your time.