Disable Worksheet Tab Menu

C

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
 
K

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
 
R

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
 
R

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


Ron de Bruin said:
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
Click to expand...
 
J

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.
 
C

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
 
Top