Unhide sheets based on NT login

M

MaxBrit

Hi

I have a workbook and would like to achieve the same as item below
Also to have some users sheets unhidden but protected.

- "I have apps where another sheet, "control" always remains
very hidden. This sheet defines which of the hidden
sheets will be made visible to whichever user opens the
file....using the NT logon as the identity. If somebody
not on the list opens the file, then only the splash
sheet is visible. Patrick Molloy,Microsoft Excel MVP"-

Have found how to get the users logon name, but cannot see how to us
this to compare to the users permissions on a 'control' sheet.

Thanks for any advice.

Maxwel
 
J

JonoB

Off the top of my head, I think what you would want to do is create a
control sheet with all the possible NT usernames in one column and
their permissions in another. So, those with permission to open have a
1 (for example) and those with no pemission have a 0.

Then, run some code on the Workbook_Open() event that check the NT
login with the control sheet. If there is a 1 next to their name, then
proceed past the splash screen (which will be a userform) and unhide
the veryhidden sheets. If there is a zero next to their name on the
control sheet, then dont close the userform, and dont unhide the
veryhidden sheets.
 
B

Bob Phillips

A simple way would be to have the control sheet structured as rows with

Login, 1st sheet, 2nd sheet, etc.

and then have code like this, assuming the NT Login is in UserName variable

Dim iRow As Long
Dim iCol As Long

On Error Resume Next
With Worksheets("Control")
iRow = Application.Match(UserName, .Range("A1:A100"), 0)
If iRow > 0 Then
For iCol = 2 To .Cells(iRow, Columns.Count).End(xlToLeft).Column
Worksheets(.Cells(iRow, iCol).Value).Visible =
xlSheetVisible
Next iCol
End If
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

MaxBrit

Thanks for the reply.

New to Excel VB, was hoping someone could point at some examples tha
could be adapted to suit. Especially the lookup of the user name an
returning the permission value.
Have done something similar with MS Access and a tab control, a
below.

- Private Sub Form_Open(Cancel As Integer)


Dim ChooseSecure As Integer

ChooseSecure = DLookup("Edit_Secure", "Staff", "Staff_Name
UserLogin()")

Select Case ChooseSecure
Case 1
TabCtl261.Pages(0).Visible = True
TabCtl261.Pages(1).Visible = True

Case 2
TabCtl261.Pages(0).Visible = True
TabCtl261.Pages(1).Visible = False

End Select

End Sub-

Any help with the equivalent 'ChooseSecure' lookup appreciated.

TIA

Maxwel
 
M

MaxBrit

Bob

Many Thanks, posts crossed.

Have got that working, will build in hide sheets before save event.

Maxwel
 
Top