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)?