Macro to direct end user to only certain tabs

illini

New Member
Joined
Sep 1, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
VBA Code:
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.
 
Upvote 0
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:
VBA Code:
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)
 
Upvote 0
OK, try this version, which will hide all sheets other than the Front Page and selected name:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top