Disable Worksheet Tab Menu

[cjsmile2106]

Hello Everyone,

Hope someone can help me out. I'm trying to find a code out ther
that will disable the Worksheet tab menu. Also to have it onl
disabled when the workbook is open and clear itself when the workboo
closes. I guess like a Workbook_Open/Workbook_Close function. An
help out there would be great.

Thanks,
Davi

 

[kkknie]

Found by recording a macro...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWindow.DisplayWorkbookTabs = True
End Sub

Private Sub Workbook_Open()
ActiveWindow.DisplayWorkbookTabs = False
End Sub

 

[Ron de Bruin]

Hi

The menu when you right click on the Arrows ?
If not use 34 for the right click on a sheet tab

Copy this in the Thisworkbook module


Private Sub Workbook_Activate()
Application.CommandBars(27).Enabled = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars(27).Enabled = True
End Sub

 

[Ron de Bruin]

Oops, you want to hide it I think

Hide it in Tools>Options
It will be saved with the file this setting

--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi

The menu when you right click on the Arrows ?
If not use 34 for the right click on a sheet tab

Copy this in the Thisworkbook module


Private Sub Workbook_Activate()
Application.CommandBars(27).Enabled = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars(27).Enabled = True
End Sub

 

[JE McGimpsey]

I'd recommend using the Workbook_Activate and Workbook_Deactivate event
macros, instead of Open and BeforeClose. That way it only applies to
that workbook, even if there are multiple workbooks open in the XL
instance.

 

[cjsmile2106]

Thank you everyone for your assistance. I'm still having troubl
disabling the menu though. The following macro works great, but I nee
to be able to view the tabs. I need to disable the tab menu, but stil
view the tabs themselves, and I haven't been able to figure that on
out yet. Help?!?

Thank you!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWindow.DisplayWorkbookTabs = True
End Sub

Private Sub Workbook_Open()
ActiveWindow.DisplayWorkbookTabs = False
End Su

 

[Ron de Bruin]

He I was right then <g>

Did you try my example ?