Lock a worksheet tab so it is always visible

B

Bolter LAC

Board Regular
Joined
Aug 13, 2008
Messages
140
Hello, I need to lock a worksheet tab (Main menu) in place so that it is always visible. There are multiple tabs that are all linked to the main menu by hyperlink. When work is finished in a worksheet I want the main menu tab to be visible and usable rather than using the scroll functions to get back to the main menu.

Thanks
 
Sort by date Sort by votes
Option 4 - I don't like doing it this way personally, but hey, it's there, try it and see. Go Windows, New Window, and Excel will open a new window with the same workbook (just adding :2) at the end so you can differentiate.

Depending how you have set up your machine, Excel can show the two instances as two buttons on your taskbar. In one, give the parent tab the focus. In the other, move around the children to your heart's content. Every time you want to go to the parent, use the other tab on the taskbar.

When you make any change in the workbook, this is reflected in both windows, as they are only two different views of the same workbook.

If, like for me, Excel combines all your books into one tab showing how many sheets are open, you just click on the Excel button on the taskbar, select the one with the parent sheet and use it to go there. Select the one with the child tabs to navigate to there.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Another suggestion for a solution would be to create a named range pointing to cell A1 on your main page. Then from any other sheet, simply press F5, click on the named range and then click the OK button and the Main Menu sheet is activated and placed in focus.

There are several different options, you'll have to decide which works best for your application.
 
Upvote 0
Tmurj & riaz,

I appreciate the suggestions. What I was hoping for was a one click solution. When I have finished entering data on one athletes sheet, one click on the main menu tab would have me back at the main page (menu) that lists all the athletes. There may not be a one click solution.

Thanks for the suggestions.

To Blade Hunter,

Yes that is exactly what I am after.

Thanks all for your replies.


Bolter
 
Upvote 0
Then I would suggest a button on each page in exacly the same position (Just so it looks like it is the same floating button) and when the user clicks it it simply selects the main page. You can't have a locked tab but there are other ways to do this.
 
Upvote 0
For a really quick and easy solution, you may want to define a hyperlink on each page that points to cell "A1" on the Main Menu sheet. This does not even require any programming and seems to accomplish exactly what you are looking to do.
 
Upvote 0
Don't know if I understand the question, but I made this solution to make my tabs visible every time I open my workbook. Just in case someone need it..

Code: 
Private Sub Workbook_Open()
    Dim currentSheet As Worksheet
    Dim currentWorkbook As Workbook
    Set currentSheet = ActiveSheet
    Set currentWorkbook = currentSheet.Parent
    currentWorkbook.Sheets("Sheet 1").Activate
    currentWorkbook.Sheets(currentSheet.Index).Activate
    Set currentSheet = Nothing
    Set currentWorkbook = Nothing
End Sub
 
Last edited:
Upvote 0
Hi,

It seems to me that a Table Of Contents (TOC) is what you need.

While creating the TOC is best done using code, you do not need the code anymore in the workbook afterwards, except when you will be changing sheets...

Code: 
Option Explicit
 
Sub create_TOC()
'Erik Van Geit
'060906
 
Dim i As Integer
Dim msg As String
Dim fc_order As Range
Dim fc_alphabet As Range
Dim sht As Object
 
'**** EDIT the following lines ****
Const TOC = "Table of Contents"
Const TocShort = "TOC"
Const CellLink = "A1"
'**** END EDIT ****
 
    On Error Resume Next
    msg = Sheets(TOC).Name
        If Err Then
        Err.Clear
        msg = "A new sheet will be added :""" & TOC & """, with hyperlinks to all sheets in this workbook."
        Else
        Worksheets(TOC).Activate
        msg = Chr(10) & Chr(10) & "Your sheet " & Chr(10) & TOC & Chr(10) & _
        "(now displayed) will be updated."
        End If
    On Error GoTo 0
If MsgBox(msg & Chr(10) & "Do you want to continue ?", 36, TOC) = vbNo Then Exit Sub
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
On Error Resume Next
Worksheets(TOC).Delete
On Error GoTo 0
 
Worksheets.Add before:=Sheets(1)
    With Sheets(1)
    .Name = TOC
    .Cells.Interior.ColorIndex = 15
    ActiveWindow.DisplayHeadings = False
        With .Cells(2, 6)
        .Value = UCase(TOC)
        .Font.Size = 18
        .HorizontalAlignment = xlCenter
        End With
    Set fc_order = .Cells(3, 4)
    Set fc_alphabet = .Cells(3, 8)
    fc_order = "order of appearance"
        For i = 2 To ActiveWorkbook.Worksheets.Count
        .Hyperlinks.Add Anchor:=Cells(i + 2, 4), Address:="", _
        SubAddress:=Worksheets(i).Name & "!A1", TextToDisplay:=Worksheets(i).Name
        Next i
    fc_alphabet = "alphabetically"
    .Range(fc_order.Offset(1, 0), fc_order.End(xlDown)).Copy fc_alphabet.Offset(1, 0)
    .Range(fc_alphabet.Offset(1, 0), fc_alphabet.End(xlDown)).Sort Key1:=fc_alphabet.Offset(1, 0)
 
    End With
 
    msg = "Do you want a hyperlink to " & TOC & " on each sheet in cell " & CellLink & "?" & Chr(10) & _
    "(if cell " & CellLink & " is empty)"
 
    If MsgBox(msg, 36, "Hyperlink on each sheet") = vbYes Then
        For Each sht In Worksheets
            With sht
                If .Range(CellLink) = "" And sht.Name <> TOC Then
                .Unprotect
                .Hyperlinks.Add Anchor:=.Range(CellLink), Address:="", _
                SubAddress:="'" & TOC & "'!A1", TextToDisplay:=TocShort
                .Protect
                End If
            End With
        Next sht
    End If
 
Sheets(TOC).Activate
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub
The TOC created with this code, will list the sheets in index order and also in alphabetical order. Clicking on a sheetname will bring you to that sheet.
In cell A1 (or another one if you want) on each sheet there will be a hyperlink to the TOC. (EDIT: so this is a one-click-solution)

For more ideas search the Board for "TOC" or "Table of contents"

kind regards,
Erik
 
Last edited:
Upvote 0
erik.van.geit said:
Hi,

It seems to me that a Table Of Contents (TOC) is what you need.

While creating the TOC is best done using code, you do not need the code anymore in the workbook afterwards, except when you will be changing sheets...

Code: 
Option Explicit
 
Sub create_TOC()
'Erik Van Geit
'060906
 
Dim i As Integer
Dim msg As String
Dim fc_order As Range
Dim fc_alphabet As Range
Dim sht As Object
 
'**** EDIT the following lines ****
Const TOC = "Table of Contents"
Const TocShort = "TOC"
Const CellLink = "A1"
'**** END EDIT ****
 
    On Error Resume Next
    msg = Sheets(TOC).Name
        If Err Then
        Err.Clear
        msg = "A new sheet will be added :""" & TOC & """, with hyperlinks to all sheets in this workbook."
        Else
        Worksheets(TOC).Activate
        msg = Chr(10) & Chr(10) & "Your sheet " & Chr(10) & TOC & Chr(10) & _
        "(now displayed) will be updated."
        End If
    On Error GoTo 0
If MsgBox(msg & Chr(10) & "Do you want to continue ?", 36, TOC) = vbNo Then Exit Sub
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
On Error Resume Next
Worksheets(TOC).Delete
On Error GoTo 0
 
Worksheets.Add before:=Sheets(1)
    With Sheets(1)
    .Name = TOC
    .Cells.Interior.ColorIndex = 15
    ActiveWindow.DisplayHeadings = False
        With .Cells(2, 6)
        .Value = UCase(TOC)
        .Font.Size = 18
        .HorizontalAlignment = xlCenter
        End With
    Set fc_order = .Cells(3, 4)
    Set fc_alphabet = .Cells(3, 8)
    fc_order = "order of appearance"
        For i = 2 To ActiveWorkbook.Worksheets.Count
        .Hyperlinks.Add Anchor:=Cells(i + 2, 4), Address:="", _
        SubAddress:=Worksheets(i).Name & "!A1", TextToDisplay:=Worksheets(i).Name
        Next i
    fc_alphabet = "alphabetically"
    .Range(fc_order.Offset(1, 0), fc_order.End(xlDown)).Copy fc_alphabet.Offset(1, 0)
    .Range(fc_alphabet.Offset(1, 0), fc_alphabet.End(xlDown)).Sort Key1:=fc_alphabet.Offset(1, 0)
 
    End With
 
    msg = "Do you want a hyperlink to " & TOC & " on each sheet in cell " & CellLink & "?" & Chr(10) & _
    "(if cell " & CellLink & " is empty)"
 
    If MsgBox(msg, 36, "Hyperlink on each sheet") = vbYes Then
        For Each sht In Worksheets
            With sht
                If .Range(CellLink) = "" And sht.Name <> TOC Then
                .Unprotect
                .Hyperlinks.Add Anchor:=.Range(CellLink), Address:="", _
                SubAddress:="'" & TOC & "'!A1", TextToDisplay:=TocShort
                .Protect
                End If
            End With
        Next sht
    End If
 
Sheets(TOC).Activate
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub
The TOC created with this code, will list the sheets in index order and also in alphabetical order. Clicking on a sheetname will bring you to that sheet.
In cell A1 (or another one if you want) on each sheet there will be a hyperlink to the TOC. (EDIT: so this is a one-click-solution)

For more ideas search the Board for "TOC" or "Table of contents"

kind regards,
Erik
Click to expand...

Your code helped a lot.
Just what I was looking for. Made a few changes like keep sheets unprotected and rename TOC to Index.
Now i'm having a trouble with some sheets that are named for ex. Arruda dos Vinhos .
Sheets with spaces or "-" are not linked.
Can you help?

<colgroup><col width="174"></colgroup><tbody>
</tbody>
 
Upvote 0
Blade Hunter said:
Replace all the spaces in the URL with %20
Click to expand...

Thx.
Actualy I was able to do it by replacing your

Code: 
.Hyperlinks.Add Anchor:=Cells(i + 2, 4), Address:="", _
        SubAddress:=Worksheets(i).Name & "!A1", TextToDisplay:=Worksheets(i).Name


with
Code: 
.Hyperlinks.Add Anchor:=Cells(i + 2, 4), Address:="",_  
 SubAddress:="'" & ActiveWorkbook.Sheets(i).Name & "'!B1", TextToDisplay:=ActiveWorkbook.Sheets(i).Name

Working great now
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
How to automatically transfer data between two worksheets using Office Scripts based on user input
Replies
2
Views
275
nguyendang
N
N
  • Solved
VBA Question
Replies
3
Views
142
RoryA
RoryA
J
  • Question
VBA Macros running slow out of nowhere
Replies
3
Views
248
mumps
mumps
B
  • Question
Dynamic Output based on cross tab inputs
Replies
0
Views
216
Bkisley
B
M
  • Solved
Normal access to sheet but with hidden tab/shortcut
Replies
2
Views
455
mrfahaji
M

Forum statistics

Threads
1,215,362
Messages
6,124,502
Members
449,166
Latest member
hokjock

Share this page

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