Excel Help - Worksheets by UserID 3

[arvarr]

Hi
Hoping someone can assist.

When a user opens the workbook, user's access is controlled by:
1. Worksheets as stated on the Access worksheet (Column B)
2. Username = UNameWindows

Access worksheet will be maintained to either remove or end-date a user.
New users will be added and granted access to respective worksheet/s.

Thanks,
arv

 

[SkipVought]

Hi,

Do you know about Excel Worksheet Events?

Describe how you want the workbook to work:

1) With a stated user
2) With some other user

A user Jane Smith OPENs the workbook. What do they see? What can they do? What can they not do?



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[kjv1611]

Assuming you mean you want the Windows User from a given row of your Access sheet to access those listed in the Worksheets column, I think the easiest way will be something like this:
[ol 1]
[li]Loop through each row[/li]
[li]An array variable to hold the worksheet names value from each row. To fill it, you'll use the Split Command. Then to make sure spaces don't ruin the party, I'd use the Trim command to remove any spaces per item.[/li]
[li]Then you'll need to loop through all worksheets, hiding all of them.[/li]
[li]Then a second loop that loops through your array for that row that unhides the sheets that user has access to.[/li]
[li]You may want a separate piece for the admin person(s) that says if they are an admin, it shows all sheets, and skips all the above (could use a Select statement to account for multiple possible Windows users or else include the admin users in your access sheet along with "all" or something to dictate they see everything, including the Access list.[/li]
[li]If security is a bit more important, and it's not just for ease of use/visibility, you'd want to add code to protect/unprotect according to the same conditions.[/li]
[/ol]

I realize that's not very specific as to the precise code you need, but just mapping out an idea, method to handle the overall project.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Some thoughts on this kind of workbook:

Make a Splash Sheet that displays "You must enable macros" or displays "You are not authorized" depending on the Username.
On the Workbook_Open Event if the User is not authorized, Display that Splash.
On the Workbook_Open Event if the Authorized User did not enable macros, display that Splash.
Otherwise make Visible the Sheet(s) of the Authorized User and make VeryHidden the Splash sheet.
On the Workbook_Close Event, make Visible the Splash Sheet and all other sheets make VeryHidden and SaveAs to a specified path and name.

Using the VeryHidden Sheet property means that a user will have no way of manually making a Hidden Sheet Visible and performing any view or change to these VeryHidden Sheets. This can only be done via access to the VBA Project. You'll want to password protect your VBA Project.

Caveats:
A savvy Excel user can probably crack a password.
A savvy Excel user can probably find the link to the PivotTable database and write queries to get "unauthorized" data.
Your users can do a SaveAs to their own location in order to work on breaking your workbook. You might want to intercept such an attempt by using the Workbook_BeforeSave Event to impose your specified path and name.

Like I previously stated, this is not a trivial task, depending on how air-tight you want the Workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
No, I am unfamiliar with Excel Worksheet Events

There will be a Data worksheet which comprise of data for all the worksheets
All other worksheets other than Data, Notes and Index will comprise of Pivot Tables

If Jane Smith opens the workbook:
1. Jane Smith can only see worksheet 1234 (Say it is a Pivot Table for only Department A).
2. Jane Smith can click on the Pivot Table in worksheet 1234 to drill through the data that makes up Department A only

Thanks,
arv

 

[SkipVought]

What if SkipVought opens your workbook--what happens?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
If SkipVought or anyone not on the listed on the "Access" list, it can be a message box or splash that displays "You are not authorized"

Thanks,
arv

 

[Andrzejek]

I am unfamiliar with Excel Worksheet Events

If you want to keep going with your Excel approach, you better get cracking with Workbook & Worksheet Events in Excel VBA

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[arvarr]

My bad, I actually had the code in the workbook events Private Sub Workbook_Open()
but my issue is i had them hard coded
and i am not sure how to have the code read them from the Access worksheet.
Reason is so that I can maintain the user access from the Access worksheet rather than having to go to the code.

e.g.
wskeep1 = ThisWorkbook.Sheets("1235").Name
wskeep1 = ThisWorkbook.Sheets("1301-01").Name
wskeep1 = ThisWorkbook.Sheets("4123").Name

Case Is = "Admin"
For Each ws In Worksheets
ws.Visible = True
Next ws

Case Is = "amyers"
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> wskeep1 Then
If ws.Name <> wskeepname2 Then
If ws.Name <> wskeepname3 Then
ws.Visible = xlSheetVeryHidden
End If
End If
End If
Next ws

 

[SkipVought]

You really need to keep DATA values out of your VBA code!

Reason? Whenever you have new users, new data or new data views, you've got to modify your code, rather than merely modifying your data.

You become a slave to your poorly written code!
Your workbook becomes a periodic nightmare!



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
I totally agree hence needing help to start with the Access table.

Thanks,
arv

 

[SkipVought]

Explain what you need to know or do.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
I am unsure how to use the Access Sheet to provide the same behavior as per the code above.
So, instead of data values in the code, i want to utilize the Access sheet to restrict users.


Thanks,
arv

 

[SkipVought]

You have the Windows UserID.

Dim rUID As Range, aWS As Variant, i As Integer

With Worksheets("Access")
   'Find UID
   For each rUID in .[Table1[Windows User ID]]
       If rUID.Value = UserID Then
          'Put Worksheets for rUID in array aWS
          aWS = Split(Trim(Union(rUID.EntireRow, .[Table1[Worksheets]])), ",").Value
          'Make Worksheets in array for UID Visible
          For i = 0 To UBound(aWS)
              Worksheets(aWS(i)).Visible = True
          Next
          Exit For
       End If
   Next
End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Much appreciated Skip.

I'll give it a shot.

Thanks,
arv

 

[arvarr]

Hi Skip
Get a run-time error 13 Type mismatch on this line
aWS = Split(Trim(Union(rUID.EntireRow, .[Table1[Worksheets]])), ",").Value

Thanks,
arv

 

[SkipVought]

Sorry, I made a terrible blunder using Union rather than Intersect. And I had Trim in the wrong place.

'
              aWS = Split([b]Intersect[/b](rUID.EntireRow, .[Table1[Worksheets]]), ",")
              'Make Worksheets in array for UID Visible
              For i = 0 To UBound(aWS)
                  Worksheets([b]Trim[/b](aWS(i))).Visible = True
              Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Another design suggestion regarding sheet names.

Each sheet, in addition to the Visible and Name properties also has a CodeName property.

1) I'd leave the user sheets' CodeName as is.

2) You need a splash sheet that would be the ONLY Visible sheet when the workbook opens AND the ONLY Visible sheet when the workbook is SaveAs and Closed. BTW, your workbook must ALWAYS have at least ONE Visible sheet at all times. Keep that in mind as you manipulate the Visible property of your sheets.

3) I would make the CodeName of all other sheets wsAccess, wsIndex, wsSplash etc. ALL the ws CodeName sheets are administrative sheets.

Does that make sense?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip


CODE
'
aWS = Split(Intersect(rUID.EntireRow, .[Table1[Worksheets]]), ",")
'Make Worksheets in array for UID Visible
For i = 0 To UBound(aWS)
Worksheets(Trim(aWS(i))).Visible = True
Next


I updated with the revised code and updated the windows replaced windows user id for Amanda Jackson to my windows ID.
I would expect to see only worksheet 1301 but i still see all the worksheets.

Thanks,
arv

 

[SkipVought]

Well it works for me.

What was your workbook sheet configuration BEFORE you ran the code in question?

That is, what sheets were VISIBLE?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!