Opening up profiles upon clicking on the personnel's name

I have this data sheet with 7 personnels name in it. What I require is that upon clicking on the name of the person, the profiles that are attached to this person will open up.

Before any name is clicked, these sheets should not be visible to the user and upon clicking on the other names, the sheets that was previously open will disappear.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.


Basically - post a file that replicates your setup in terms of the no. of sheets in the file - where the code is to be run from etc etc... you will need to use VBA.

I can see one immediate issue... the sheet naming convention...

ie for:

Mohd Sufian Bin Mahmood

the sheets for the above are prefixed with Suffian, correct ?

So how would you deduce the prefix for say:

Tan Suan Theng

would they be Suan, Theng etc...

(searching for "any" of the ee name in the sheet names is inherently risky IMO)

If you were to say store the sheet name prefix say above the M/S flag you could use that - if you don't have that I think things will get quite messy.

Based on the above post and your file...

First I inserted two sheets - one called START and one called END - the START sheet was placed before the first of all the sheets to be hidden/made visible (ie after Dec09) ... END was placed after the last sheet to be hidden.

Second I inserted the appropriate sheet prefix in the cell above marital status, eg H5 is Sufian, D19 is Khameed etc...

Lastly, I inserted the below by right clicking on Team Data tab - selecting View Code and pasting below into resulting window. You must ensure Macros are enabled thereafter.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error GoTo ExitPoint
If Intersect(Target, Range("A5,E5,A12,E12,A19,E19,A26")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For lngI = Sheets("Start").Index To Sheets("End").Index Step 1
    With Sheets(lngI)
        .Visible = IIf(InStr(.Name, Target.Offset(, 3).Value) > 0, True, xlVeryHidden)
    End With
Next lngI
ExitPoint:
Cancel = True
Application.ScreenUpdating = True
End Sub

When you double click on a name in either Column A or D the appropriate sheets should display... the remainder are "very hidden" meaning they are not visible via the Sheets Unhide/Hide menu (can only be made visible by VBA) - if you don't need this you can revised xlVeryHidden to xlHidden.

To avoid confusion I have for your benefit put my prior post into your file... I would however still suggest trying to replicate the steps yourself for sake of familiarity.

As you can see in the attached there is no prefix assigned to D5 on Team Data... double clicking on a name for which no prefix is assigned will by the nature of the code unhide all sheets. If you double click on any name with a sheet prefix assigned you should see the visible sheets alter accordingly.