Excel Table of Contents

[msam137]

I have an excel workbook with mutiple worksheets and each work sheet
has several employee names. Trying to create an employee directory
with the managers and the people they manager for each group. I have
master page with all of department managers on it. My goal is when I
click the persons name it will go to their worksheet and highlight
their entire row.

 

[Otto Moehrbach]

The layout you describe is not clear. I take it that the Master sheet has
all the managers' names listed in some column. It's the layout of the other
sheets that is not clear. Does each manager listed in the Master sheet have
a sheet for him and his people? If so then you want that sheet selected
when that manager's name is clicked on. Is that right? Is the sheet name
the same as the name in the managers' listing in the Master sheet? What is
"their entire row"? Is that the same specific row on all sheets? If not,
how can that row be found? HTH Otto

 

[Maurice Samuels]

The layout you describe is not clear. I take it that the Master sheet has
all the managers' names listed in some column. It's the layout of the other
sheets that is not clear. Does each manager listed in the Master sheet have
a sheet for him and his people? If so then you want that sheet selected
when that manager's name is clicked on. Is that right? Is the sheet name
the same as the name in the managers' listing in the Master sheet? What is
"their entire row"? Is that the same specific row on all sheets? If not,

Thanks Otto for your reply

Yes I have one column with all the managers name on the master sheet.
The sheets names are named by group they manage. One group could have
multiple managers. Currently I have hyper links set up so when you
click on the managers name it goes to the sheet and the cell that the
name is in it. What I would like is for it not only go to the cell but
highlight the entire row. The entire row would have the managers name
and the each adjacent cell has more information. In Other words I have
a hyper link to sheet 3 the managers name i clicked on his in cell A6
the excel template would go there and sit on cell A6 well I want it to
highlight row 6 so it stands out for that period of time.

 

[Otto Moehrbach]

Maurice
The following macro will select the sheet and then select Row 6 of that
sheet. You said you wanted row 6 "highlighted". I don't know what you
meant by that so I just selected row 6. Come back if this is not what you
want.
As written, the managers names are "Mgr 1", "Mgr 2", "Mgr 3" and they are in
Column A of the Master sheet starting with A2 down.
The associated sheets are named "One", "Two", "Three". Change these as
needed and add to the list in the code as needed. You can have as many
managers as you want and you can have as many sheets applying to the same
manager as you want. Be aware that the managers' names in Column A of the
Master sheet must match EXACTLY the names that you have in this macro.
Also, the sheet names in this macro and the actual sheet names must match
EXACTLY. If the sheet name in the macro doesn't match that of an actual
sheet, you will get an error. If the Managers name doesn't match, you will
get a message box telling you that there is no sheet associated with that
manager.
Note that this macro is a sheet event macro and must be placed in the sheet
module of the Master sheet. To access that module, right-click on the sheet
tab and select View Code. Paste this macro into that module. "X" out of
the module to return to your sheet. HTH Otto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rMgrs As Range
Dim TheSht As String
If Target.Count > 1 Then Exit Sub
Set rMgrs = Range("A2", Range("A" & Rows.Count).End(xlUp))
If Not Intersect(Target, rMgrs) Is Nothing Then
Select Case Target.Value
Case "Mgr 1": TheSht = "One"
Case "Mgr 2": TheSht = "Two"
Case "Mgr 3": TheSht = "Three"
Case Else: GoTo NoSht
End Select
Sheets(TheSht).Select
ActiveSheet.Rows("6:6").Select
Exit Sub
NoSht:
MsgBox "There is no sheet associated with Manager " & Target.Value & "."
End If
End Sub