Is possible to freeze position of the EXCEL tab?

[vladk]

Hi,

I would like to freeze the first EXCEL tab. The workbook has about 20 tabs. The first tab is the index. I would like to freeze it like the column of the worksheet. So when user scrolls the tabs the very fisrt one remains visible and accessible.

Thank you
Vlad

 

[xlbo]

Don't think this is possible - the only way around it would be to make the index sheet the sheet to the immediate left of the active sheet - and that would require code in all the sheet_activate events (for all 20 sheets)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vladk]

xlbo,

Thank you for the responce. I will think about it.

Vlad

 

[SkipVought]

That could actually be done ONCE in the Workbook_SheetActivate event.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    sName = "Sheet1"  ' your first sheet name here
    EnableEvents = False
    If Sh.Name = sName Then Exit Sub
    If Sh.Index = Sheets(sName).Index + 1 Then Exit Sub
    Sheets(sName).Move before:=Sh
    Sh.Activate
    EnableEvents = True
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RobBroekhuis]

Interesting problem. I've no solution either. Closest I can come up with is a custom toolbar (at position=msoBarLeft, so that it is to the left of the tabs) with a single button to take you to the index tab. But you lose half an inch of screen real estate doing that.

Rob

 

[VBAjedi]

Have an idea. . . back in a couple minutes. . .

VBAjedi

 

[VBAjedi]

Ok, I got close, but I gotta leave. What I have so far only has to be put in one place (the Workbook object). It moves the sheet named "Sheet1" to be just before the newly-activated sheet. I just didn't have time to figure out how to set "y" so that the tabs always scrolled so that "Sheet1" was at the left. . .

Private Sub Workbook_SheetActivate(ByVal sh As Object)
Application.EnableEvents = False
x = sh.Index
y = x - 1
If x < 2 Then y = 0

Sheets(&quot;Sheet1&quot;).Move Before:=Sheets(x)
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Sheets:=(y)
sh.Activate
Application.EnableEvents = True
End Sub

Hope that gets you pointed in the right direction!


VBAjedi

 

[xlbo]

Jedi - I think that's what Skip's code does....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vladk]

Thank you guys. I added ScreenUpdating = false, true.
It works almost perfectly but some blinking I could not avoid. I actually was looking for the frozen position for the first tab that should be always visible.

Anyway, thanks for the code.

 

[TheBitDoctor]

I agree with XLBO, I don't think you can freeze the first tab as you would a split screen on the worksheet window. You say the tab is for an Index sheet. How is that index used? If your using it for navigation you may be able to make use of the jump to feature of the sheet tab navigation arrows. If you right click on the navigation buttons you'll get a list of sheets in the workbook. If you want more control you can try the code below.

'In the ThisWorkbook macro sheet:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

'   Delete it if it exists
    On Error Resume Next
        Application.CommandBars("Jump To Sheet").Delete
    On Error GoTo 0

End Sub

Private Sub Workbook_Open()
    MakeJumpToMenu
End Sub


'In a VBA Module:
Sub MakeJumpToMenu()
Dim myBar As CommandBar
Dim myControl As CommandBarControl

'   Delete it if it already exists
    On Error Resume Next
        Application.CommandBars("Jump To Sheet").Delete
    On Error GoTo 0

'   Create a new command bar
    Set myBar = Application.CommandBars.Add(Name:="Jump To Sheet", Position:=msoBarTop, Temporary:=True)
    Set myControl = myBar.Controls.Add(Type:=msoControlDropdown)
    With myControl
        .Caption = "Jump To"
        For Each sh In Sheets
            .AddItem sh.Name, 1
        Next sh
        .DropDownLines = Sheets.Count
        .DropDownWidth = 75
        .ListHeaderCount = 0
        .OnAction = "JumpToSheet"
    End With
    myBar.Visible = True
    
End Sub

Public Sub JumpToSheet()
    ThisWorkbook.Sheets(Application.CommandBars("Jump To Sheet").Controls(1).List( _
Application.CommandBars("Jump To Sheet").Controls(1).ListIndex)).Activate
End Sub

 

[vladk]

TheBitDoctor,

I have built similar thing about a week ago:

Application.CommandBars("Worksheet Menu Bar&quot.Controls.Add _
Type:=msoControlButton, ID:=2950, before:=TOOLBAR_CONTROLS_COUNT, Temporary:=True

With Application.CommandBars("Worksheet Menu Bar&quot.Controls(TOOLBAR_CONTROLS_COUNT)
.Caption = "&Return to Index"
.FaceId = 1377
.Style = msoButtonIconAndCaption
.OnAction = "ActivateIndex"
End With


Public Sub ActivateIndex()
Sheets("Index&quot.Activate
End Sub


I will look into your code to compare ( I also have a code to destroy the object when workbook closes).

Thank you

Vlad