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.

 

[GeorgeWhite]

Works great thank you!

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

 

[GeorgeWhite]

So I am now using the following code

Private Sub Worksheet_Change(ByVal Target As Range)    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("C11")
  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
    Sheets(rng.Value).Select
  End If
  Sheets("MENU").Visible = xlSheetVeryHidden
End Sub

with another bit of code for the pop up page linked to a command button to return back too the menu once the user has finished

Private Sub CommandButton1_Click()Sheets("MENU").Visible = True
ThisWorkbook.Worksheets("KPX 5 10-5").Visible = xlSheetVeryHidden
Worksheets("MENU").Range("C11:F11").ClearContents
End Sub

but I recieve "Run-time error '1004': Unable to set the Visible property of the Worksheet class" does anyone know why this is?

 

[dmt32]

So I am now using the following code

  Sheets("MENU").Visible = xlSheetVeryHidden

but I recieve "Run-time error '1004': Unable to set the Visible property of the Worksheet class" does anyone know why this is?

At least ONE sheet must remain visible - You have hidden the MENU sheet which, for code I posted, should remain visible.

Dave

 

[GeorgeWhite]

But I would like to hide the MENU sheet when another sheet is selected? Almost like its opened into a new workbook? I will then insert a "Home" command button to take you back too the MENU tab and you will be able to make another selection.

At least ONE sheet must remain visible - You have hidden the MENU sheet which, for code I posted, should remain visible.

Dave

 

[dmt32]

But I would like to hide the MENU sheet when another sheet is selected? Almost like its opened into a new workbook? I will then insert a "Home" command button to take you back too the MENU tab and you will be able to make another selection.

You can try following

Ensure that your data validation list includes MENU as a selection choice

DELETE line shown in red

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("C11")
  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
    Sheets(rng.Value).Select
  End If
  Sheets("MENU").Visible = xlSheetVeryHidden
End Sub

Place following in Thisworkbook CODE PAGE

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If UCase(Sh.Name) <> "MENU" Then Worksheets("MENU").Visible = xlSheetVeryHidden
End Sub

Each home button on your worksheets will need following code

Sub HomeButton_Click()
 Worksheets("MENU").Range("C11").Value = "MENU"
End Sub

This is for a Form control button but you can change to ActiveX commandButton if wanted

Note: Menu sheet will need to be the FIRST sheet in the workbook.

Dave

 

[GeorgeWhite]

It works great! Thank you Dave.

You can try following

Ensure that your data validation list includes MENU as a selection choice

DELETE line shown in red

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("C11")
  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
    Sheets(rng.Value).Select
  End If
  Sheets("MENU").Visible = xlSheetVeryHidden
End Sub

Place following in Thisworkbook CODE PAGE

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If UCase(Sh.Name) <> "MENU" Then Worksheets("MENU").Visible = xlSheetVeryHidden
End Sub

Each home button on your worksheets will need following code

Sub HomeButton_Click()
 Worksheets("MENU").Range("C11").Value = "MENU"
End Sub

This is for a Form control button but you can change to ActiveX commandButton if wanted

Note: Menu sheet will need to be the FIRST sheet in the workbook.

Dave

 

[dmt32]

It works great! Thank you Dave.

Welcome - thanks for feedback

Dave