Macro to direct end user to only certain tabs

[illini]

Have a workbook with multiple tabs for each end user. Want a way to have a "front page" of sorts where end user can select themselves (from drop down I assume) and that will open up only their tab?

 

[Joe4]

Welcome to the Board!

Let's say that you have the drop-down field (Data Validation list) in cell A1, and all your tabs names EXACTLY match this list.
Then you can use the following code, which must be in the proper sheet module.
An easy way to ensure that is to go to the sheet with the drop-down field, right-click on the sheet tab name at the bottom of the tab, select "View Code", and paste this VBA code in the VB Editor window that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   See if cell with drop-down (A1) was changed
    If Not Intersect(Target, Range("A1")) Is Nothing Then
'       Select sheet named for value in cell A1
        Sheets(Range("A1").Value).Select
    End If
   
End Sub

If your drop-down is in a different cell, just change all the "A1" references in the code above to the address of your drop-down cell.

 

[illini]

Welcome to the Board!

Let's say that you have the drop-down field (Data Validation list) in cell A1, and all your tabs names EXACTLY match this list.
Then you can use the following code, which must be in the proper sheet module.
An easy way to ensure that is to go to the sheet with the drop-down field, right-click on the sheet tab name at the bottom of the tab, select "View Code", and paste this VBA code in the VB Editor window that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   See if cell with drop-down (A1) was changed
    If Not Intersect(Target, Range("A1")) Is Nothing Then
'       Select sheet named for value in cell A1
        Sheets(Range("A1").Value).Select
    End If
  
End Sub

If your drop-down is in a different cell, just change all the "A1" references in the code above to the address of your drop-down cell.

That works! Thanks. Was hoping for the extra step of hiding all the non active tabs. (clutter bothers me!!!) If I hide the tabs and then try to select it I get an error (Select Method of Worksheet class failed)

 

[Joe4]

OK, try this version, which will hide all sheets other than the Front Page and selected name:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim fpName As String
    Dim tName As String

'   See if cell with drop-down (A1) was changed
    If Not Intersect(Target, Range("A1")) Is Nothing Then
'       Capture name of current sheet
        fpName = ActiveSheet.Name
'       Capture name of selected person/sheet
        tName = Range("A1").Value
'       First unhide all sheets
        For Each ws In ActiveWorkbook.Worksheets
            ws.Visible = True
        Next ws
'       Hide all sheets except Front Page and selected sheet
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> fpName And ws.Name <> tName Then ws.Visible = False
        Next ws
'       Select sheet named for value in cell A1
        Sheets(tName).Select
    End If
    
End Sub