MrExcel Publishing
Your One Stop for Excel Tips & Solutions

workbook tabs false


Posted by Brian P on May 09, 2001 9:55 AM

I would like to have the tabs, row headers and column headers not show when my workbook is opened. At one point I thought I had it but now it shows them for a few seconds before it hides them. Here’s the code I’m using. What is opening before this code? Help. Thanks.

The code is under “This Workbook” and not in a separate module.
Option Explicit
Sub Workbook_Open()
Application.ScreenUpdating = True ‘ I don’t think I need this
ActiveWindow.DisplayWorkbookTabs = False
Worksheets("Report").Activate “This is the tab I want to show but I don’t want the other tabs listed at the bottom.
End Sub


Also,
Dave H - thanks for your previous help moving around in visible cells. I appreciate it.


Posted by Dave Hawley on May 09, 2001 10:27 AM


Hi Brian

The code below is about as good as you will get:

Private Sub Workbook_Open()
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With
Worksheets("Report").Activate
End Sub


The user will see them for a second as the Window must activate first. It's not possible to display only one Tab. It's all or nothing I'm afraid. You could of course create a simple Commandbar with the sheets name and have Docked and Locked at the very bottom of your screen.


Dim cButton As CommandBarButton

Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars("MyBar").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim bIsClosed As Boolean
If Cancel = True Then Exit Sub
On Error Resume Next
Application.CommandBars("MyBar").Delete
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("MyBar").Visible = True
End Sub

Private Sub Workbook_Open()
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With
On Error Resume Next
Application.CommandBars("MyBar").Delete
Application.CommandBars.Add "MyBar", msoBarBottom
Set cButton = Application.CommandBars("MyBar").Controls.Add(msoControlButton)
With cButton
.OnAction = "ReportShow"
.Caption = "Report"
.Style = msoButtonCaption
End With
Application.CommandBars("MyBar").Visible = True
Application.CommandBars("MyBar").Protection = msoBarNoMove
Worksheets("Report").Activate
End Sub


The "ReportShow" would be a standard macro that would activate you sheet.


Dave

OzGrid Business Applications

Posted by Brian P on May 09, 2001 11:04 AM

Thanks very much Dave! as usual - Huge help.

The "ReportShow" would be a standard macro that would activate you sheet. Dave