Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make Excel display a specific worksheet?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make Excel display a specific worksheet?
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Bowtie63" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make Excel display a specific worksheet?
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) "Bowtie63" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make Excel display a specific worksheet?
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! "Chip Pearson" wrote: 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) "Bowtie63" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to make a link to a specific tab in excel? | Excel Worksheet Functions | |||
I need a shortcut to make a excel file open to a specific sheet | Excel Discussion (Misc queries) | |||
How do I make excel startup in a specific sheet? | Excel Worksheet Functions | |||
Display specific rows from table on other worksheet | Excel Discussion (Misc queries) | |||
How do I make a cell date specific to input a value on a specific. | Excel Discussion (Misc queries) |