Disabling Worksheet Menu Bar in a Chart Sheet

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

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)

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

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) **************************************************

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) **************************************************

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) : **************************************************

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)