vba codes to show sheet/tab based on data validation

[albert28]

Hi guys,

is there any vba codes that can show tab/sheet that is based on the filter selection.

for example:

Cell A1 have a data validation of Apple, orange and Mango

i have 3 tabs named Apple, orange and Mango.

so everytime i select Apple on the MAin page filter. Apple tab will show. and if orange then orange tab will show.

thanks.

[David A Coop]

Here is a quick macro to place on Sheet1. I assume that the other three sheets are sheets 2 thru 4 and named as you have suggested.

This macro allows you to delete your selection, or go to the nominated sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Target.Address = "$A$1" Then
        If Target.Value = "" Then
            Sheets("Sheet1").Select
            Exit Sub
        End If
        sht = Target.Value
        Sheets(sht).Select
    End If
    
End Sub

I hope this helps, please let me know!

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[albert28]

Hi David,
Thanks for the code but how can do if the sheet name are apple, orange and mango?

thanks again

[David A Coop]

Hi David,
Thanks for the code but how can do if the sheet name are apple, orange and mango?

thanks again

Hi Albert,

You said that you had a drop down list (using data validation). I assumed this was NOT on one of the sheets Apple, Orange or Mango.

If not, just take out the IF statement on lines 3 to 6, and paste the code on the three sheets.

This seems like a waste of effort to me! It is easier to just click the Tab you want!

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".