How do I make Excel display a specific worksheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook with a few worksheets, one sheet per person in my group.
Can I set up Excel so that when an individual opens the workbook, they can
see only their own specific worksheet without seeing the others? Or is there
a way that I can create a query that asks the user to select from a list and
enter a password to bring up a specific sheet? Thank you in advance for your
help!
 
Easiest would be to have a master sheet where each enters the code to unhide
only his sheet. You would need to use VERYhidden to it would only work with
code.
 
If the worksheet's are named the same as the user's Windows user name (not
the User Name in the Options dialog), you can use code like the following in
the ThisWorkbook module. If there is no sheet named with the user's
UserName, the code exits and all sheets remain visible.

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"
( _
ByVal lpBuffer As String, _
nSize As Long) As Long

Private Sub Workbook_Open()
Dim UName As String
Dim L As Long
Dim R As Long
Dim WS As Worksheet
L = 255
UName = String$(L, vbNullChar)
R = GetUserName(UName, L)
If R <> 0 Then
UName = Left(UName, L - 1)
If SheetExists(UName, ThisWorkbook) = False Then
Exit Sub
End If
Me.Worksheets(UName).Visible = xlSheetVisible
For Each WS In Me.Worksheets
If StrComp(WS.Name, UName, vbTextCompare) <> 0 Then
WS.Visible = xlSheetVeryHidden
End If
Next WS
End If
End Sub

Private Function SheetExists(SheetName As String, _
Optional WB As Workbook = Nothing) As Boolean
On Error Resume Next
SheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB) _
.Worksheets(SheetName).Name))
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Hi Chip, thank you! I'll give this a try. I would like one clarification
though. For Windows user name, is this the name we use to log into Windows
XP? We are on a network and there's corporate standard set for logons. Is
there a way to use the code, but have us designate the name? I'm thinking
that the managers would want to use their employee's name instead of a logon
name. Please let me know. Thank you for your help!
 
Back
Top