Controlling Worksheet Tab Position

prcrstntr

New Member
Joined
Nov 15, 2003
Messages
28
The Worksheet names that I have make the tabs wide enough that five tabs show at any given time when scrolling through the twelve Worksheets in my Workbook. I would like one of the Worksheet tabs to be seen at all times, preferably the right most of the five tabs that can be seen. Essentially, the same as Freezing a Pane in a Worksheet, but Freezing a Worksheet Tab instead. Is this possible?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,695
Office Version
  1. 365
Platform
  1. Windows
Can achieve with VBA like this..

Below assumes that none of your worksheets are hidden and that the structure of the workbook is not protected
I would not expect standard sheet protection to cause any issues

Create a copy of your workbook and test on that

Place code below in ThisWorkbook Module and amend Master (to name of frozen 5th sheet) BEFORE exiting VBA
{ALT}{F11} to go to VBA window \ double-click on ThisWorbook in VBA Project Window \ Paste code in the window that opens up
Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If ThisWorkbook.Worksheets.Count < 6 Then Exit Sub
    Dim c As Long, s As Long, wsM As Worksheet
    On Error Resume Next
    Set wsM = Sheets("[I][COLOR=#ff0000]Master[/COLOR][/I]")
    On Error GoTo Handling
    
    Application.EnableEvents = False
    
    c = Worksheets.Count
    If wsM.Index <> c Then wsM.Move after:=Worksheets(c)
    wsM.Activate
    s = Sh.Index
    
    Select Case s
       Case Is < 5
           Sheets(1).Activate
           wsM.Move after:=Sheets(4)
       Case Is < (c - 4)
           Sheets(s - 3).Activate
           wsM.Move after:=Sheets(s)
    End Select
    Sh.Activate
Handling:
    Application.EnableEvents = True
End Sub

Run Events to re-enable event trigger if inadvertently disabled during testing!!
Place code below in a standard Module
(when in VBA window) Inserts a Module with {ALT} I M \ Paste code in the window that opens up
Code:
Sub Events()
    Application.EnableEvents = True
End Sub

Save the workbook as macro enabled
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,049
Office Version
  1. 2016
Platform
  1. Windows
It should be noted that the procedure in post # 2 clears the clipboard - so copy/paste from one sheet to another will not be possible.

If this is unacceptable, the code could run from a normal macro (suggest via a shortcut key) as and when necessary (i.e. when "Master" sheet is not visible).
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,695
Office Version
  1. 365
Platform
  1. Windows
@footoo makes a valid point

What you are requesting is a bit messy - but it was fun finding a way to achieve it

Presumably you are forever referring to the 5th sheet
In which case try a simple toggle attached to a shortcut

Place in a new standard module and amend sheet name
Code:
Option Explicit
Public OldSheet As Worksheet

Sub ToggleMaster()
Const sh = "[COLOR=#ff0000]Master[/COLOR]"
    If Not ActiveSheet.Name = sh Then
        Set OldSheet = ActiveSheet
        Sheets(sh).Activate
    Else
        If Not OldSheet Is Nothing Then OldSheet.Activate
    End If
End Sub

- allows you to toggle between the 2 sheets
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top