Hide Sheet tabs view completely

[JackDanIce]

Hi,

Have code that hides the sheet tabs:

ActiveWindow.DisplayWorkbookTabs = False

Unfortunately, the User can change this via: Excel Options->Advanced, section Display.

Is there anyway to prevent the user re-enabling the display of workbook tabs?

In this instance Sheet.Visible = xlSheetVeryHidden won't suffice.

Suggestions, workarounds (or accurately predicted lottery numbers), replies welcome,
Jack

 

[Yongle]

Q What exactly are you trying to achieve?
eg are you trying to prevent a user being able to right-click on sheet tab? - if so see post#3

In the meantime ... try this sledge-hammer approach (does not prevent the user amending the option but the next action reverses it!)

Place these in ThisWorkbook module (NOT in a standard module, NOT in a sheet module)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    HideTabs
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    HideTabs
End Sub
Private Sub Workbook_Open()
    HideTabs
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    HideTabs
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    HideTabs
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    HideTabs
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    HideTabs
End Sub
Private Sub HideTabs()
    ActiveWindow.DisplayWorkbookTabs = False
End Sub

 

[Yongle]

To disable the context menu which appears when user right-clicks on sheet tab

Place code in ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Ply").Enabled = True
End Sub
 
Private Sub Workbook_Open()
    Application.CommandBars("Ply").Enabled = False
End Sub

 

[JackDanIce]

@Yongle, thank you for replying but I think you've missed what I asked which is preventing the User changing the view of displayed tabs - not changing menu options or using xlSheetVeryHidden

Already manipulating lots of menu disables and right-click features. Trying to avoid including more of this in the code.

Your sledge hammer code, I already stated it as being in code. I didn't include Sub and End Sub as it seemed superfluous, and generally speaking if it's already in code, then it's going to be in the relevant event procedure.

Hiding the entire sheet tabs (& preventing User unhiding) will save a lot of other control options and reduce the code - that is why I tried to be specific with the question