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

 

[arvarr]

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

 

[arvarr]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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]

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

 

[SkipVought]

None at all

Just change

If rUID.Value = [b]UNameWindows[/b] Then

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
Not sure what i am not doing right.
I have updated the code as per above.
I have update my the windows user id to my windows user id.
When i open the file, it still shows all worksheet instead of worksheet 1301.

Thanks,
arv

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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]

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

 

[combo]

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 [tt]ThisWorkbook.UnProtect/Protect[/tt] with password should be added.

combo

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Andrzejek]

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

 

[arvarr]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
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

 

[SkipVought]

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...

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 = [b]UNameWindows[/b] 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

Sheet.Visible = xlSheetVeryHidden

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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]

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