Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Table of Contents
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Table of Contents
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 wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Table of Contents
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 "Maurice Samuels" wrote in message ... On Sep 25, 4:22 pm, "Otto Moehrbach" wrote: 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 wrote in message ... I have an excel workbook with multiple 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table of Contents in Excel 2007 | Excel Discussion (Misc queries) | |||
Creating a table of contents in Excel | Excel Worksheet Functions | |||
Table of Contents Excel | Excel Discussion (Misc queries) | |||
Show whole Table of Contents for Excel Help | Excel Discussion (Misc queries) | |||
EXCEL Table of Contents | Excel Worksheet Functions |