Only one worksheet can be active at any given time. So, you can't keep the Front Sheet visible when you go to another worksheet.
If you right-click on the bottom lower left corner (on the navigation arrows) you will get a pop up list of all worksheets in the workbook. From there you can navigate to any sheet easily.
This code uses the worksheet_BeforeRightClick event to navigate to the sheet.
Code goes in the Front Sheet code module. It also uses a Custom Function (below) to test if the worksheet exists.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim strName As String
Dim lastrow As Long
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C11:C" & lastrow)) Is Nothing Then
If Target.Value <> "" Then
Cancel = True
strName = Target.Value
If wsExists(strName) Then
Application.Goto reference:=Worksheets(strName).Range("A1"), scroll:=True
Else
MsgBox ("The sheet named: " & strName & " does not exist"), vbExclamation
End If
Else
Exit Sub
End If
End If
End Sub
Place this code into a standard module.
Option Explicit
Function wsExists(wksName As String) As Boolean
On Error Resume Next
wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
Credit for function here by RoyUK - post #3
Bookmarks