MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Disabling Worksheet Menu Bar in a Chart Sheet


Posted by Stan on October 01, 2001 7:59 AM


I use the code below to disable the Menu Bar in a worksheet. But it doesn't work if the sheet is a chart workshheet. Two questions - how do I
1. specify I only want to hide the menu bars in the chart sheet?
2. ensure that other open workbooks are not affected (i.e. how would I use "ThisWorkbook.Name" in the above)?

Cheers

Stan

************************************
Private Sub Workbook_Open()

Application.CommandBars("Worksheet Menu Bar").Enabled = False

End Sub


Posted by Juan Pablo on October 01, 2001 8:33 AM

Stan, the Menu Bar for chart sheets is called

Chart Menu Bar

instead of Worksheet Menu Bar.

Regarding your other question, i tend to put something like:

If ActiveWorkbook = ThisWorkbook then
'rest of the code
End If

Juan Pablo

Posted by Stan on October 01, 2001 9:23 AM

Re: That makes sense but what about...

Juan

My next dilema is I want to create a custom menu in the chart sheet. I have a code that works fine in the WorkSheet. How do I specify the "Chart" sheet(see below for partial example)?

Cheers - Stan

*******************************************
Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)

**************************************************

Posted by Juan Pablo on October 01, 2001 9:32 AM

Don't understand your problem... (NT)

My next dilema is I want to create a custom menu in the chart sheet. I have a code that works fine in the WorkSheet. How do I specify the "Chart" sheet(see below for partial example)? Cheers - Stan ******************************************* Do Until IsEmpty(MenuSheet.Cells(Row, 1)) With MenuSheet MenuLevel = .Cells(Row, 1) Caption = .Cells(Row, 2) **************************************************

Posted by Stan on October 01, 2001 9:36 AM

Re: It's OK Juan - I figured it out


I was fooled a bit. The code has "Application.CommandBars(1)" in one of the command lines. This is where I have to substitute "(1)" with "("Graph Menu Bar")"

Stan Juan My next dilema is I want to create a custom menu in the chart sheet. I have a code that works fine in the WorkSheet. How do I specify the "Chart" sheet(see below for partial example)? Cheers - Stan ******************************************* Do Until IsEmpty(MenuSheet.Cells(Row, 1)) With MenuSheet MenuLevel = .Cells(Row, 1) Caption = .Cells(Row, 2) **************************************************

Posted by Stan on October 01, 2001 9:44 AM

A Thought

Juan

This part of the code. What I am wondering now is if it is possible to have custom menus for both the graph and worksheet with only a slight modification of the code.

Cheers

Stan
**************************************
Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars("Chart Menu Bar"). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption : Juan : My next dilema is I want to create a custom menu in the chart sheet. I have a code that works fine in the WorkSheet. How do I specify the "Chart" sheet(see below for partial example)? : Cheers - Stan : ******************************************* Do Until IsEmpty(MenuSheet.Cells(Row, 1)) With MenuSheet MenuLevel = .Cells(Row, 1) Caption = .Cells(Row, 2) : **************************************************

Posted by Juan Pablo on October 01, 2001 11:21 AM

Re: A Thought

I guess it would be possible, i don't see anything wrong in your code.

Juan Pablo Juan This part of the code. What I am wondering now is if it is possible to have custom menus for both the graph and worksheet with only a slight modification of the code. Cheers Stan Do Until IsEmpty(MenuSheet.Cells(Row, 1)) With MenuSheet MenuLevel = .Cells(Row, 1) Caption = .Cells(Row, 2)