excel menubar
 

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

--
Message posted from http://www.ExcelForum.com

excel menubar
 

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 " wrote in message
...
Private Sub viewDS()
Application.CommandBars(1).ActionControl.State = msoButtonDown
If ActiveWorkbook.Charts("DS SALES$").Visible = xlSheetVisible
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 show
up in both the excel worksheet and excel shart. anyone know of a way to
get it working? It doesn't work if i try the index CommandBars(1)


---
Message posted from http://www.ExcelForum.com/

excel menubar
 

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

--
Message posted from http://www.ExcelForum.com

excel menubar
 

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)

"majikman " wrote in message
...
Bob,

i tried both of the below lines and neither works. it gives me a
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 .State
member to the worksheet or chart that it is caled from. do you see what
i might be doing wrong? thanks

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


---
Message posted from http://www.ExcelForum.com/