| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet group
Q1: Can a file be setup so a macro is always running?
I wrote a macro that allows a user to pick a day of the week with a text box and based on that selection "groups" of worksheets linked to the selected day are shown and the other sheets are hidden (that way a user can look at worksheets only "linked" to a particular day of the week). Q2: Is there a way to be able to just click on a worksheet tab and have a set of worksheets that are "linked" to that tab automatically shown (they would be hidden otherwise)? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet group
Bern,
Q1. You could use an event, and tie your code to that. For example, the workbook open event could check the day of the week and you could use logic to decide which sheets to show and which to hide. Specifically what event and how to use it requires a bit more explanation of what you want on your part. Q2. Yes, you could use the worksheet activate event to change the visible property of the 'linked' sheets. But the code, again, depends on what you mean by linked. HTH, Bernie MS Excel MVP "Bern Notice" wrote in message ... Q1: Can a file be setup so a macro is always running? I wrote a macro that allows a user to pick a day of the week with a text box and based on that selection "groups" of worksheets linked to the selected day are shown and the other sheets are hidden (that way a user can look at worksheets only "linked" to a particular day of the week). Q2: Is there a way to be able to just click on a worksheet tab and have a set of worksheets that are "linked" to that tab automatically shown (they would be hidden otherwise)? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet group
I am interested in your response to Q2, but I'm not sure what you mean by
"worksheet activation event". When I say linked, I mean that we have a spreadsheet that has a summary worksheet for each day of the week (i.e. Monday, Tuesday, etc.). For each of those daily summary worksheets, there are a number of other worksheets (i.e. worksheets for clients that report on a given day of the week) that feed information to the daily summary worksheet. What we would like to do is have only the daily summary worksheets visible until a user clicks on a particular day's summary worksheet. Then the client worksheets that feed into that day's summary are then visible. When the user clicks on another day's summary, the client worksheets that feed into that summary would be visible and the other "day's" client worksheets would be hidden again. Right now I wrote a macro to accomplish this, but the user has to run the macro and then choose from a text box. Just didn't know if there was a way to accomplish this with only having the user click on the various summary tabs? Thank you for your reply. Bernie "Bernie Deitrick" wrote: Bern, Q1. You could use an event, and tie your code to that. For example, the workbook open event could check the day of the week and you could use logic to decide which sheets to show and which to hide. Specifically what event and how to use it requires a bit more explanation of what you want on your part. Q2. Yes, you could use the worksheet activate event to change the visible property of the 'linked' sheets. But the code, again, depends on what you mean by linked. HTH, Bernie MS Excel MVP "Bern Notice" wrote in message ... Q1: Can a file be setup so a macro is always running? I wrote a macro that allows a user to pick a day of the week with a text box and based on that selection "groups" of worksheets linked to the selected day are shown and the other sheets are hidden (that way a user can look at worksheets only "linked" to a particular day of the week). Q2: Is there a way to be able to just click on a worksheet tab and have a set of worksheets that are "linked" to that tab automatically shown (they would be hidden otherwise)? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet group
Bernie,
Put this code into the codemodule of the Thisworkbook object, changing the sheet names as needed. HTH, Bernie MS Excel MVP Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim myS As Worksheet If Sh.Name Like "Client*" Then Exit Sub For Each myS In Worksheets If myS.Name Like "Client*" Then myS.Visible = xlSheetHidden Next myS If Sh.Name = "Monday" Then Worksheets("Client 1").Visible = xlSheetVisible Worksheets("Client 2").Visible = xlSheetVisible ElseIf Sh.Name = "Tuesday" Then Worksheets("Client 3").Visible = xlSheetVisible Worksheets("Client 4").Visible = xlSheetVisible ElseIf Sh.Name = "Wednesday" Then Worksheets("Client 5").Visible = xlSheetVisible Worksheets("Client 6").Visible = xlSheetVisible ElseIf Sh.Name = "Thursday" Then Worksheets("Client 7").Visible = xlSheetVisible Worksheets("Client 8").Visible = xlSheetVisible ElseIf Sh.Name = "Friday" Then Worksheets("Client 9").Visible = xlSheetVisible Worksheets("Client 10").Visible = xlSheetVisible End If End Sub "Bern Notice" wrote in message ... I am interested in your response to Q2, but I'm not sure what you mean by "worksheet activation event". When I say linked, I mean that we have a spreadsheet that has a summary worksheet for each day of the week (i.e. Monday, Tuesday, etc.). For each of those daily summary worksheets, there are a number of other worksheets (i.e. worksheets for clients that report on a given day of the week) that feed information to the daily summary worksheet. What we would like to do is have only the daily summary worksheets visible until a user clicks on a particular day's summary worksheet. Then the client worksheets that feed into that day's summary are then visible. When the user clicks on another day's summary, the client worksheets that feed into that summary would be visible and the other "day's" client worksheets would be hidden again. Right now I wrote a macro to accomplish this, but the user has to run the macro and then choose from a text box. Just didn't know if there was a way to accomplish this with only having the user click on the various summary tabs? Thank you for your reply. Bernie "Bernie Deitrick" wrote: Bern, Q1. You could use an event, and tie your code to that. For example, the workbook open event could check the day of the week and you could use logic to decide which sheets to show and which to hide. Specifically what event and how to use it requires a bit more explanation of what you want on your part. Q2. Yes, you could use the worksheet activate event to change the visible property of the 'linked' sheets. But the code, again, depends on what you mean by linked. HTH, Bernie MS Excel MVP "Bern Notice" wrote in message ... Q1: Can a file be setup so a macro is always running? I wrote a macro that allows a user to pick a day of the week with a text box and based on that selection "groups" of worksheets linked to the selected day are shown and the other sheets are hidden (that way a user can look at worksheets only "linked" to a particular day of the week). Q2: Is there a way to be able to just click on a worksheet tab and have a set of worksheets that are "linked" to that tab automatically shown (they would be hidden otherwise)? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet group
Thanks for your help Bernie. I am fairly new at writing macros and appreciate your input. I typed in the code you provided below, but not sure if I have to do something special to get it to "run". Below is everything in my "Thisworkbook" section of the macro window (the code you provided me is at the bottom). Maybe I was supposed to put your code somewhere else I am not aware of. Please advise. Macros: ___________________________________________ Sub HideSheets() ' ' HideSheets Macro ' ' Sheets("Client1").Visible = False Sheets("Client2").Visible = False Sheets("Client3").Visible = False Sheets("Client4").Visible = False Sheets("Client5").Visible = False Sheets("Client6").Visible = False End Sub Sub ShowMonday() ' ' ShowMonday Macro ' ' Sheets("Client1").Visible = True Sheets("Client2").Visible = True Sheets("Monday").Select End Sub Sub ShowTuesday() ' ' ShowTuesday Macro ' ' Sheets("Client3").Visible = True Sheets("Client4").Visible = True Sheets("Tuesday").Select End Sub Sub ShowWednesday() ' ' ShowWednesday Macro ' ' Sheets("Client5").Visible = True Sheets("Client6").Visible = True Sheets("Wednesday").Select End Sub Sub SortBy() ' ' SortBy Macro ' Main Sorting Macro ' Dim Message, TitlebarTxt, DefaultTxt, SortVal, YNAnswer As String ' Assigning message text: Message = _ "Enter a number to show that days groups:" & _ vbCrLf & _ " 1 -- Monday" & vbCrLf & _ " 2 -- Tuesday" & vbCrLf & _ " 3 -- Wednesday" TitlebarTxt = "Show which day's groups" DefaultTxt = "Enter 1, 2 or 3" SortVal = InputBox(Message, TitlebarTxt, DefaultTxt) Select Case SortVal Case "1" Call ShowMonday Call ShowTuesday Call ShowWednesday Call HideSheets Call ShowMonday Case "2" Call ShowMonday Call ShowTuesday Call ShowWednesday Call HideSheets Call ShowTuesday Case "3" Call ShowMonday Call ShowTuesday Call ShowWednesday Call HideSheets Call ShowWednesday Case Else YNAnswer = MsgBox("You didn't enter a valid number. Try again?", vbYesNo) If YNAnswer = 6 Then Call SortBy End If End Select End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim myS As Workbook If Sh.Name Like "Client*" Then Exit Sub For Each myS In Worksheets If myS.Name Like "Client*" Then myS.Visible = xlSheetHidden Next myS If Sh.Name = "Monday" Then Worksheets("Client1").Visible = xlSheetVisible Worksheets("Client2").Visible = xlSheetVisible ElseIf Sh.Name = "Tuesday" Then Worksheets("Client3").Visible = xlSheetVisible Worksheets("Client4").Visible = xlSheetVisible ElseIf Sh.Name = "Wednesday" Then Worksheets("Client5").Visible = xlSheetVisible Worksheets("Client6").Visible = xlSheetVisible End If End Sub __________________________________________________ ________ "Bernie Deitrick" wrote: Bernie, Put this code into the codemodule of the Thisworkbook object, changing the sheet names as needed. HTH, Bernie MS Excel MVP Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim myS As Worksheet If Sh.Name Like "Client*" Then Exit Sub For Each myS In Worksheets If myS.Name Like "Client*" Then myS.Visible = xlSheetHidden Next myS If Sh.Name = "Monday" Then Worksheets("Client 1").Visible = xlSheetVisible Worksheets("Client 2").Visible = xlSheetVisible ElseIf Sh.Name = "Tuesday" Then Worksheets("Client 3").Visible = xlSheetVisible Worksheets("Client 4").Visible = xlSheetVisible ElseIf Sh.Name = "Wednesday" Then Worksheets("Client 5").Visible = xlSheetVisible Worksheets("Client 6").Visible = xlSheetVisible ElseIf Sh.Name = "Thursday" Then Worksheets("Client 7").Visible = xlSheetVisible Worksheets("Client 8").Visible = xlSheetVisible ElseIf Sh.Name = "Friday" Then Worksheets("Client 9").Visible = xlSheetVisible Worksheets("Client 10").Visible = xlSheetVisible End If End Sub "Bern Notice" wrote in message ... I am interested in your response to Q2, but I'm not sure what you mean by "worksheet activation event". When I say linked, I mean that we have a spreadsheet that has a summary worksheet for each day of the week (i.e. Monday, Tuesday, etc.). For each of those daily summary worksheets, there are a number of other worksheets (i.e. worksheets for clients that report on a given day of the week) that feed information to the daily summary worksheet. What we would like to do is have only the daily summary worksheets visible until a user clicks on a particular day's summary worksheet. Then the client worksheets that feed into that day's summary are then visible. When the user clicks on another day's summary, the client worksheets that feed into that summary would be visible and the other "day's" client worksheets would be hidden again. Right now I wrote a macro to accomplish this, but the user has to run the macro and then choose from a text box. Just didn't know if there was a way to accomplish this with only having the user click on the various summary tabs? Thank you for your reply. Bernie "Bernie Deitrick" wrote: Bern, Q1. You could use an event, and tie your code to that. For example, the workbook open event could check the day of the week and you could use logic to decide which sheets to show and which to hide. Specifically what event and how to use it requires a bit more explanation of what you want on your part. Q2. Yes, you could use the worksheet activate event to change the visible property of the 'linked' sheets. But the code, again, depends on what you mean by linked. HTH, Bernie MS Excel MVP "Bern Notice" wrote in message ... Q1: Can a file be setup so a macro is always running? I wrote a macro that allows a user to pick a day of the week with a text box and based on that selection "groups" of worksheets linked to the selected day are shown and the other sheets are hidden (that way a user can look at worksheets only "linked" to a particular day of the week). Q2: Is there a way to be able to just click on a worksheet tab and have a set of worksheets that are "linked" to that tab automatically shown (they would be hidden otherwise)? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet group
Bernie,
If you have it in the correct location, simply selecting one of the sheets Monday, Tuesday, or Wednesday will run the code. But it appears that you placed it in a regular codemodule with your other macro code, in which case Excel will not execute it automatically. You need to double-click the ThisWorkbook object in the project explorer, and a new blank window should appear. THAT is the window that you want to paste it in. See here for more about events http://www.cpearson.com/excel/Events.aspx HTH, Bernie MS Excel MVP "Bern Notice" wrote in message ... Thanks for your help Bernie. I am fairly new at writing macros and appreciate your input. I typed in the code you provided below, but not sure if I have to do something special to get it to "run". Below is everything in my "Thisworkbook" section of the macro window (the code you provided me is at the bottom). Maybe I was supposed to put your code somewhere else I am not aware of. Please advise. Macros: ___________________________________________ Sub HideSheets() ' ' HideSheets Macro ' ' Sheets("Client1").Visible = False Sheets("Client2").Visible = False Sheets("Client3").Visible = False Sheets("Client4").Visible = False Sheets("Client5").Visible = False Sheets("Client6").Visible = False End Sub Sub ShowMonday() ' ' ShowMonday Macro ' ' Sheets("Client1").Visible = True Sheets("Client2").Visible = True Sheets("Monday").Select End Sub Sub ShowTuesday() ' ' ShowTuesday Macro ' ' Sheets("Client3").Visible = True Sheets("Client4").Visible = True Sheets("Tuesday").Select End Sub Sub ShowWednesday() ' ' ShowWednesday Macro ' ' Sheets("Client5").Visible = True Sheets("Client6").Visible = True Sheets("Wednesday").Select End Sub Sub SortBy() ' ' SortBy Macro ' Main Sorting Macro ' Dim Message, TitlebarTxt, DefaultTxt, SortVal, YNAnswer As String ' Assigning message text: Message = _ "Enter a number to show that days groups:" & _ vbCrLf & _ " 1 -- Monday" & vbCrLf & _ " 2 -- Tuesday" & vbCrLf & _ " 3 -- Wednesday" TitlebarTxt = "Show which day's groups" DefaultTxt = "Enter 1, 2 or 3" SortVal = InputBox(Message, TitlebarTxt, DefaultTxt) Select Case SortVal Case "1" Call ShowMonday Call ShowTuesday Call ShowWednesday Call HideSheets Call ShowMonday Case "2" Call ShowMonday Call ShowTuesday Call ShowWednesday Call HideSheets Call ShowTuesday Case "3" Call ShowMonday Call ShowTuesday Call ShowWednesday Call HideSheets Call ShowWednesday Case Else YNAnswer = MsgBox("You didn't enter a valid number. Try again?", vbYesNo) If YNAnswer = 6 Then Call SortBy End If End Select End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim myS As Workbook If Sh.Name Like "Client*" Then Exit Sub For Each myS In Worksheets If myS.Name Like "Client*" Then myS.Visible = xlSheetHidden Next myS If Sh.Name = "Monday" Then Worksheets("Client1").Visible = xlSheetVisible Worksheets("Client2").Visible = xlSheetVisible ElseIf Sh.Name = "Tuesday" Then Worksheets("Client3").Visible = xlSheetVisible Worksheets("Client4").Visible = xlSheetVisible ElseIf Sh.Name = "Wednesday" Then Worksheets("Client5").Visible = xlSheetVisible Worksheets("Client6").Visible = xlSheetVisible End If End Sub __________________________________________________ ________ "Bernie Deitrick" wrote: Bernie, Put this code into the codemodule of the Thisworkbook object, changing the sheet names as needed. HTH, Bernie MS Excel MVP Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim myS As Worksheet If Sh.Name Like "Client*" Then Exit Sub For Each myS In Worksheets If myS.Name Like "Client*" Then myS.Visible = xlSheetHidden Next myS If Sh.Name = "Monday" Then Worksheets("Client 1").Visible = xlSheetVisible Worksheets("Client 2").Visible = xlSheetVisible ElseIf Sh.Name = "Tuesday" Then Worksheets("Client 3").Visible = xlSheetVisible Worksheets("Client 4").Visible = xlSheetVisible ElseIf Sh.Name = "Wednesday" Then Worksheets("Client 5").Visible = xlSheetVisible Worksheets("Client 6").Visible = xlSheetVisible ElseIf Sh.Name = "Thursday" Then Worksheets("Client 7").Visible = xlSheetVisible Worksheets("Client 8").Visible = xlSheetVisible ElseIf Sh.Name = "Friday" Then Worksheets("Client 9").Visible = xlSheetVisible Worksheets("Client 10").Visible = xlSheetVisible End If End Sub "Bern Notice" wrote in message ... I am interested in your response to Q2, but I'm not sure what you mean by "worksheet activation event". When I say linked, I mean that we have a spreadsheet that has a summary worksheet for each day of the week (i.e. Monday, Tuesday, etc.). For each of those daily summary worksheets, there are a number of other worksheets (i.e. worksheets for clients that report on a given day of the week) that feed information to the daily summary worksheet. What we would like to do is have only the daily summary worksheets visible until a user clicks on a particular day's summary worksheet. Then the client worksheets that feed into that day's summary are then visible. When the user clicks on another day's summary, the client worksheets that feed into that summary would be visible and the other "day's" client worksheets would be hidden again. Right now I wrote a macro to accomplish this, but the user has to run the macro and then choose from a text box. Just didn't know if there was a way to accomplish this with only having the user click on the various summary tabs? Thank you for your reply. Bernie "Bernie Deitrick" wrote: Bern, Q1. You could use an event, and tie your code to that. For example, the workbook open event could check the day of the week and you could use logic to decide which sheets to show and which to hide. Specifically what event and how to use it requires a bit more explanation of what you want on your part. Q2. Yes, you could use the worksheet activate event to change the visible property of the 'linked' sheets. But the code, again, depends on what you mean by linked. HTH, Bernie MS Excel MVP "Bern Notice" wrote in message ... Q1: Can a file be setup so a macro is always running? I wrote a macro that allows a user to pick a day of the week with a text box and based on that selection "groups" of worksheets linked to the selected day are shown and the other sheets are hidden (that way a user can look at worksheets only "linked" to a particular day of the week). Q2: Is there a way to be able to just click on a worksheet tab and have a set of worksheets that are "linked" to that tab automatically shown (they would be hidden otherwise)? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy a group to a new worksheet | Excel Programming | |||
| how can I group information from more than one worksheet | Excel Worksheet Functions | |||
| how to group records in a new worksheet | Excel Worksheet Functions | |||
| how to group records in a new worksheet | Excel Discussion (Misc queries) | |||
| add worksheet to group | Excel Programming | |||
![Bern Notice[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
