Hide sheets
Hi
I have a workbook for the time attendance in the main sheet there is a list of name with link when ever you click on your name your sheet will open and than you can put how many hours y have worked each day what i need is a way to hide those sheets from being seen but whenever you press on your name your sheet will be displayed. but each month i have to access each sheet so i can add rows in it and add the date and the projects +sometimes i need to add sheets for new employees Is there is any way that i can be able to do all this ? Tia |
Hide sheets
Tia
These 2 little macros will do that for you. I assumed your sheet with the names is named "Main". I also assumed that each person's sheet is named the name that is in the Main sheet. Further, I assumed that the names in the Main sheet are in Column A starting in A2. The first macro is a sheet event macro and must be placed in the sheet module of the Main sheet. To access that module, right-click on the sheet tab, select View Code, and paste this macro into that module. "X" out of the module to return to your sheet. The second macro is a workbook event macro and must be placed in the workbook module. In all versions of Excel short of 2007, access that module by right-clicking on the Excel icon that is immediately to the left of the word "File" in the menu that runs across the top of the screen. To give you access to all the sheets and NOT have the first macro fire as you select cells, simply place "Tia" (without the quotes) in D1, save the file, close the file, and open the file. The file will then open with all sheets visible and the first macro will not fire when you click on any of the name cells. To return the file to the state wherein a name selection will hide all sheets but the one with that name, simply clear D1, save the file, close the file, and open the file. If you wish, send me an email and I'll send you the small file I used to develop the macros. That file will have the macros properly placed. My email address is . Remove the "extra" from this address. HTH Otto Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim ws As Worksheet If Range("D1").Value = "Tia" Then Exit Sub If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing And _ Target.Row 1 Then For Each ws In ThisWorkbook.Worksheets If ws.Name < "Main" Then ws.Visible = False Next ws Sheets(Target.Value).Visible = True Sheets(Target.Value).Select End If End Sub Private Sub Workbook_Open() Dim ws As Worksheet If Sheets("Main").Range("D1").Value = "Tia" Then For Each ws In ThisWorkbook.Worksheets ws.Visible = True Next ws End If End Sub "Tia" wrote in message ... Hi I have a workbook for the time attendance in the main sheet there is a list of name with link when ever you click on your name your sheet will open and than you can put how many hours y have worked each day what i need is a way to hide those sheets from being seen but whenever you press on your name your sheet will be displayed. but each month i have to access each sheet so i can add rows in it and add the date and the projects +sometimes i need to add sheets for new employees Is there is any way that i can be able to do all this ? Tia |
Hide sheets
On Jul 7, 6:43*pm, "Otto Moehrbach"
wrote: Tia * * These 2 little macros will do that for you. *I assumed your sheet with the names is named "Main". *I also assumed that each person's sheet is named the name that is in the Main sheet. *Further, I assumed that the names in the Main sheet are in Column A starting in A2. The first macro is a sheet event macro and must be placed in the sheet module of the Main sheet. *To access that module, right-click on the sheet tab, select View Code, and paste this macro into that module. *"X" out of the module to return to your sheet. The second macro is a workbook event macro and must be placed in the workbook module. *In all versions of Excel short of 2007, access that module by right-clicking on the Excel icon that is immediately to the left of the word "File" in the menu that runs across the top of the screen. To give you access to all the sheets and NOT have the first macro fire as you select cells, simply place "Tia" (without the quotes) in D1, save the file, close the file, and open the file. *The file will then open with all sheets visible and the first macro will not fire when you click on any of the name cells. To return the file to the state wherein a name selection will hide all sheets but the one with that name, simply clear D1, save the file, close the file, and open the file. If you wish, send me an email and I'll send you the small file I used to develop the macros. *That file will have the macros properly placed. *My email address is . *Remove the "extra" from this address. *HTH *Otto Private Sub Worksheet_SelectionChange(ByVal Target As Range) * * * Dim ws As Worksheet * * * If Range("D1").Value = "Tia" Then Exit Sub * * * If Target.Count 1 Then Exit Sub * * * If Not Intersect(Target, Range("A:A")) Is Nothing And _ * * * * * * Target.Row 1 Then * * * * * * For Each ws In ThisWorkbook.Worksheets * * * * * * * * * If ws.Name < "Main" Then ws.Visible = False * * * * * * Next ws * * * * * * Sheets(Target.Value).Visible = True * * * * * * Sheets(Target.Value).Select * * * End If End Sub Private Sub Workbook_Open() * * * Dim ws As Worksheet * * * If Sheets("Main").Range("D1").Value = "Tia" Then * * * * * * For Each ws In ThisWorkbook.Worksheets * * * * * * * * * ws.Visible = True * * * * * * Next ws * * * End If End Sub"Tia" wrote in message ... Hi I have a workbook for the time attendance in the main sheet there is a list of name with link when ever you click on your name your sheet will open and than you can put how many hours y have worked each day what i need is a way to hide those sheets from being seen but whenever you press on your name your sheet will be displayed. but each month i have to access each sheet so i can add rows in it and add the date and the projects +sometimes i need to add sheets for new employees Is there is any way that i can be able to do all this ? Tia- Hide quoted text - - Show quoted text - The names are in Columns A12 till A 46 and from F12 TILL F46 My workbook name is tia ? What is D1 ? this is my email adress i sent an email but im not sure that you have received it Thank you in advance Tia |
Hide sheets
Tia
I sent you the file at 3:13PM Eastern time. Post back if you don't get it. Otto "Tia" wrote in message ... On Jul 7, 6:43 pm, "Otto Moehrbach" wrote: Tia These 2 little macros will do that for you. I assumed your sheet with the names is named "Main". I also assumed that each person's sheet is named the name that is in the Main sheet. Further, I assumed that the names in the Main sheet are in Column A starting in A2. The first macro is a sheet event macro and must be placed in the sheet module of the Main sheet. To access that module, right-click on the sheet tab, select View Code, and paste this macro into that module. "X" out of the module to return to your sheet. The second macro is a workbook event macro and must be placed in the workbook module. In all versions of Excel short of 2007, access that module by right-clicking on the Excel icon that is immediately to the left of the word "File" in the menu that runs across the top of the screen. To give you access to all the sheets and NOT have the first macro fire as you select cells, simply place "Tia" (without the quotes) in D1, save the file, close the file, and open the file. The file will then open with all sheets visible and the first macro will not fire when you click on any of the name cells. To return the file to the state wherein a name selection will hide all sheets but the one with that name, simply clear D1, save the file, close the file, and open the file. If you wish, send me an email and I'll send you the small file I used to develop the macros. That file will have the macros properly placed. My email address is . Remove the "extra" from this address. HTH Otto Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim ws As Worksheet If Range("D1").Value = "Tia" Then Exit Sub If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing And _ Target.Row 1 Then For Each ws In ThisWorkbook.Worksheets If ws.Name < "Main" Then ws.Visible = False Next ws Sheets(Target.Value).Visible = True Sheets(Target.Value).Select End If End Sub Private Sub Workbook_Open() Dim ws As Worksheet If Sheets("Main").Range("D1").Value = "Tia" Then For Each ws In ThisWorkbook.Worksheets ws.Visible = True Next ws End If End Sub"Tia" wrote in message ... Hi I have a workbook for the time attendance in the main sheet there is a list of name with link when ever you click on your name your sheet will open and than you can put how many hours y have worked each day what i need is a way to hide those sheets from being seen but whenever you press on your name your sheet will be displayed. but each month i have to access each sheet so i can add rows in it and add the date and the projects +sometimes i need to add sheets for new employees Is there is any way that i can be able to do all this ? Tia- Hide quoted text - - Show quoted text - The names are in Columns A12 till A 46 and from F12 TILL F46 My workbook name is tia ? What is D1 ? this is my email adress i sent an email but im not sure that you have received it Thank you in advance Tia |
All times are GMT +1. The time now is 11:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com