excel menubar

[majikman]

Private Sub viewDS()
Application.CommandBars(1).ActionControl.State = msoButtonDown
If ActiveWorkbook.Charts("DS SALES$").Visible = xlSheetVisibl
Then
ActiveWorkbook.Charts("DS SALES$").Visible = xlSheetVeryHidden
ActiveWorkbook.Charts("DS MARGIN$").Visible
xlSheetVeryHidden
ActiveWorkbook.Charts("DS Cumm Sales $").Visible
xlSheetVeryHidden
ActiveWorkbook.Charts("DS Cumm Marg $").Visible
xlSheetVeryHidden
Else
ActiveWorkbook.Charts("DS SALES$").Visible = xlSheetVisible
ActiveWorkbook.Charts("DS MARGIN$").Visible = xlSheetVisible
ActiveWorkbook.Charts("DS Cumm Sales $").Visible
xlSheetVisible
ActiveWorkbook.Charts("DS Cumm Marg $").Visible
xlSheetVisible
End If
End Sub

i'm trying to make it so that when the user clicks on the menubar item
its state gets set to msoButtonDown or msoButtonUp, but it doesn't sho
up in both the excel worksheet and excel shart. anyone know of a way t
get it working? It doesn't work if i try the index CommandBars(1

 

[Bob Phillips]

The commandbar is different for worksheets rather than for charts. Although
they are both index 1 when the particular sheet type is active, the names
are different. Set it up on both commandbars by name

Application.Commandbars("Worksheet Menu Bar")

Application.Commandbars("ChartMenu Bar")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[majikman]

Bob,

i tried both of the below lines and neither works. it gives me
compiler error saying that data member wasn't found

Application.CommandBars("Worksheet Menu Bar").ActionControl.State
msoButtonUp

Application.CommandBars("Worksheet Menu Bar").State = msoButtonUp

however,
Application.CommandBars.ActionControl.State = msoButtonUp

does work. however, that line will only apply the state of the .Stat
member to the worksheet or chart that it is caled from. do you see wha
i might be doing wrong? thanks

btw, what code do i add to make my menubar visible only in the workboo
that the vba code is existent in? i don't want the menubar to show u
when another workbook is open at the same time, which it currently i
doing

 

[Bob Phillips]

Normally, ActionControl is invoked when a button on a toolbar or a menu is
pressed, that is included in its OnAction macro. That way, you have implicit
access to the object pressed.

To make your menu only visible to that workbook would need to set the
visibility property to True/False in the workbook activate/deactivate
events.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)