×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Help - Worksheets by UserID
3

Excel Help - Worksheets by UserID

Excel Help - Worksheets by UserID

(OP)
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

RE: Excel Help - Worksheets by UserID

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

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:
  1. Loop through each row
  2. 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.
  3. Then you'll need to loop through all worksheets, hiding all of them.
  4. Then a second loop that loops through your array for that row that unhides the sheets that user has access to.
  5. 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.
  6. 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.
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

RE: Excel Help - Worksheets by UserID

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
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

RE: Excel Help - Worksheets by UserID

What if SkipVought opens your workbook--what happens?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
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

RE: Excel Help - Worksheets by UserID

Quote (arvarr)

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

RE: Excel Help - Worksheets by UserID

(OP)
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

RE: Excel Help - Worksheets by UserID

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

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

Thanks,
arv

RE: Excel Help - Worksheets by UserID

Explain what you need to know or do.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
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

RE: Excel Help - Worksheets by UserID

You have the Windows UserID.

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
Much appreciated Skip.

I'll give it a shot.

Thanks,
arv

RE: Excel Help - Worksheets by UserID

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

Thanks,
arv

RE: Excel Help - Worksheets by UserID

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

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 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
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

RE: Excel Help - Worksheets by UserID

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
What do you mean by workbook sheet configuration?

I added this line above your code.
UserID = UNameWindows

In a separate module
Function UNameWindows() As String
UNameWindows = Environ("USERNAME")
End Function

Thanks,
arv

RE: Excel Help - Worksheets by UserID

(OP)
All sheets are visible as per the file that i attached before

RE: Excel Help - Worksheets by UserID

I previously asked you

Describe how you want the workbook to work:

You never responded to this. So I assumed that you didn't have a process in mind and I used a process that I once deployed.

I implied in my post of 11 Oct 22 20:09, that...
On the Workbook_Close Event, make Visible the Splash Sheet and all other sheets make VeryHidden and SaveAs to a specified path and name.

This is the assumption. A user opens the workbook and if they are authorized, their sheet(s) are made visible, while the Splash sheet is made VeryHidden.

When the user exits by either Closing or SaveAs, the Splash is made visible and all other sheets are made VeryHidden.

Keeps everything nice and neat.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
My bad if i have not responded directly to the question.

Does adding this code causes problem to the code that you provide?

UserID = UNameWindows

In a separate module
Function UNameWindows() As String
UNameWindows = Environ("USERNAME")
End Function

Thanks,
arv

RE: Excel Help - Worksheets by UserID

None at all

Just change

CODE

If rUID.Value = UNameWindows Then 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

Again, your workbook sheet configuration needs a VISIBLE Splash and ALL OTHER SHEETS VERY HIDDEN.

Your WorkbookOpen Event should 1) make VISIBLE your Sheet(s) and makethe Splash VeryHidden.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
Hi Skip
Is the splash required in order for the code provided to work?
I thought the aws code puts the worksheet names in array
The UBound (aWS) returns 0?


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

Sorry, a bit lost.

Thanks,
arv

RE: Excel Help - Worksheets by UserID

What happens if the user doesn't enable macros? They can do anything they like in any sheet.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
Hi Skip
II will hide all sheets with the exception of Access and Notes worksheet by default.

The other worksheets will be dependent on user's window's id.

I'll need to get the code you help with to run then i will work on the other bits.

Thanks,
arv

RE: Excel Help - Worksheets by UserID

What happens if the user doesn't enable macros? They can do anything they like in any sheet.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
I see what you mean. I just tested the 3 options when macros are disabled.
Appears that even with "Disable VBA macros with notification", all sheets are shown.
The other 2 disable options, it comes up with blocked contents.

Nevertheless, i'm still stuck and not sure how to progress with the code.

Thanks,
arv

RE: Excel Help - Worksheets by UserID

When working with the workbook this way, you can consider to protect common data and structure. Protection of initial sheet prevents initial messages from changes, I like to set full protection with no selection of protected cells. In this case the user can only read the contents.
You need also to protect the structure, to avoid adding or deleting sheets by the user, otherwise you will not be able to control sheets in workbiook. A simple scenario: with disabled macros a sheet can be added, initial sheet deleted and workbook saved. Moreover, a macro in external workbook can change visibility of sheets. As you plan to change visibility of the sheets in your code, the workbook has to be unprotected for this action, so additional pair of ThisWorkbook.UnProtect/Protect with password should be added.

combo

RE: Excel Help - Worksheets by UserID

Arv, you need, very carefully taking ample time, to plan the PROCESS and the design to support the process. This process will entail Workbook and Worksheet Events and protection. You need to know what they are and how they work. As I've stated several times in this thread, THIS IS NOT A TRIVIAL TASK. Combo has added his extensive and deep knowledge of Excel raising additional issues.

You already have inconsistencies in your spreadsheet design. You have worksheets listed as user-allowed that do not exist in the workbook structure.

Your workbook:
Will it be shared on a network, accessible by all your users or will you distribute to each of your users.
Can your users save it anywhere with a different name or create multiple copies?

You are much too far from having a cohesive workbook process and design to be coding procedures for viewing or hiding sheets!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

I am with Skip.
Before you do any coding, I would write in plain English all steps, requirements, settings, etc. You have a very good start with suggestions from Skip, combo, and kjv1611
That will be the best use of your time and effort PRIOR to any coding in VBA.
'Pseudo-coding' is not 'glamorous', but pays big time at the end.

---- Andy

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

RE: Excel Help - Worksheets by UserID

(OP)
Hi All
Thanks heaps for the info and advice.

In my workbook, i have the these steps as well.
- Hide
- Unhide
- Workbook before close event
- Protect / UnProtect Worksheets with password
- Protect / UnProtect Workbook with password
- Restrict PivotTable to disable drilldown, showing of field list...

The file will be stored on network. It will be a read only file. And, users can save it a copy to any location.

I will need to incorporate if macro is not enabled to show only Access and Notes worksheet.
The file i have been working on is i have data values in the code and can't figure a way to maintain user access from the Access worksheet.

Thanks,
arv

RE: Excel Help - Worksheets by UserID

Quote:

The file i have been working on is i have data values in the code and can't figure a way to maintain user access from the Access worksheet.

I showed you a way, did I not?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
Hi Skip
What am i missing in my current file as i am unable to get the following code to work?

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

Thanks,
arv

RE: Excel Help - Worksheets by UserID

On the Access sheet, you have a Structured Table named Table1.

Using this code along with the user's userid, will make each sheet for the given user Visible, leaving all other sheets in whatever visible state they originally were, presumably VeryHidden...

CODE

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 = UNameWindows Then
          'Put Worksheets for rUID in array aWS
          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 
          Exit For
       End If
   Next
End With 

And, of course, when I say that the sheet state is VeryHidden, i mean that

CODE

Sheet.Visible = xlSheetVeryHidden 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel Help - Worksheets by UserID

(OP)
Thanks Skip.
All good now, I should be able to proceed with the remaining.

I got my head stuck that when i run the code, it will produce the behavior i want. I have to make those veryhidden and the code then makes it visible.

As always, Thanks for all your help.

Thanks to kjv1611, combo & Andrzejek for the additional info and advice too.

Thanks,
arv

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close