VBA change sheet on data validation selection

[GeorgeWhite]

Hi,

I have not used VBA in months and my first task back is trying to create a menu page which will have a data validation drop down with multiple selections on there which I want to link too certain sheets. So when selecting "test" from the drop down list then it will take me to the "test" sheet which will be a hidden tab. The menu tab will be the only tab the user can see and when returning back too it I want the drop down box to be blank again ready for the next selection.

If anyone could please help with this I would greatly appreciate it.

 

[dmt32]

Hi,
have a look here:https://contexturesblog.com/archives/2016/06/16/show-specific-sheets-in-excel/

and see if this will help you with your project.

You can download a free working example of the workbook, any adjustments for your specific need, post back to this site - plenty here to help you

Dave

 

[GeorgeWhite]

Hi Dave,

Thanks for the response but I stumbled across this page when looking into different methods and unfortunately it is not quite what I am looking for. The concept is similar but with my one I want the tabs to be hidden and when selected in the drop down list it will then appear and will make that page present.

Hi,
have a look here:https://contexturesblog.com/archives/2016/06/16/show-specific-sheets-in-excel/

and see if this will help you with your project.

You can download a free working example of the workbook, any adjustments for your specific need, post back to this site - plenty here to help you

Dave

 

[dmt32]

Hi Dave,

with my one I want the tabs to be hidden and when selected in the drop down list it will then appear and will make that page present.

untested - but try this

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("B4")
  If Target.Address = rng.Address Then
    For Each ws In Worksheets
        Select Case ws.Name
        Case "Menu", rng.Value
            ws.Visible = xlSheetVisible
        Case Else
            ws.Visible = xlSheetVeryHidden
        End Select
    Next ws
  End If
End Sub

This assumes that your menu sheet is named Menu. Change code as required

Change the cell address shown in red that refers to your data validation list

Dave

 

[GeorgeWhite]

Dave,

This just hides the tabs at the bottom when selecting something in the drop down list?
Assuming I put the code under the menu sheet?

untested - but try this

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("B4")
  If Target.Address = rng.Address Then
    For Each ws In Worksheets
        Select Case ws.Name
        Case "Menu", rng.Value
            ws.Visible = xlSheetVisible
        Case Else
            ws.Visible = xlSheetVeryHidden
        End Select
    Next ws
  End If
End Sub

This assumes that your menu sheet is named Menu. Change code as required

Change the cell address shown in red that refers to your data validation list

Dave

 

[dmt32]

Hi,
Yes code goes in your Menu Tab code page

solution, displays Menu Tab & Selected Tab name from your data validation list. All other sheets will be hidden.

If this is not quite what you want please explain further & will have a look (unless another here steps in) when I return from my bike outing

Dave

 

[GeorgeWhite]

Dave,

Yes sorry I have it working now! I had "/" in my validation list and obviously in tab names you cannot use "/". So now all I need to do is modify the code so it will change to that tab once it is selected.

Hi,
Yes code goes in your Menu Tab code page

solution, displays Menu Tab & Selected Tab name from your data validation list. All other sheets will be hidden.

If this is not quite what you want please explain further & will have a look (unless another here steps in) when I return from my bike outing

Dave

 

[dmt32]

Dave,

So now all I need to do is modify the code so it will change to that tab once it is selected.

Code should do that but as written, it is case sensitive.

Try this update

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("B4")
  If Target.Address = rng.Address Then
    For Each ws In Worksheets
        Select Case UCase(ws.Name)
        Case "MENU", UCase(rng.Value)
            ws.Visible = xlSheetVisible
        Case Else
            ws.Visible = xlSheetVeryHidden
        End Select
    Next ws
  End If
End Sub

Adjust values in red as required

Dave

 

[GeorgeWhite]

Morning Dave,

Still cant seem to get it working. I make my selection from the data validation drop down on my "MENU" tab and it now makes the selected tab visible and the "MENU" tab is still visible but doesnt switch to the selected tab?

Code should do that but as written, it is case sensitive.

Try this update

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("B4")
  If Target.Address = rng.Address Then
    For Each ws In Worksheets
        Select Case UCase(ws.Name)
        Case "MENU", UCase(rng.Value)
            ws.Visible = xlSheetVisible
        Case Else
            ws.Visible = xlSheetVeryHidden
        End Select
    Next ws
  End If
End Sub

Adjust values in red as required

Dave

 

[Jaafar Tribak]

Untested :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("B4")
  If Target.Address = rng.Address Then
    For Each ws In Worksheets
        Select Case UCase(ws.Name)
        Case "MENU", UCase(rng.Value)
            ws.Visible = xlSheetVisible
        Case Else
            ws.Visible = xlSheetVeryHidden
        End Select
    Next ws
[COLOR=#ff0000][B]    Sheets(rng.Value).Select[/B][/COLOR]
  End If
End Sub