Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
Ok, probably a stupid question but:
I'm creating a custom menu with buttons to apply custom user defined charts: Dim myMenu As Object Set myMenu = CommandBars("Worksheet menu bar").Controls.Add(Type:=msoControlPopup) With myMenu .Caption = "User defined charts" . . . End With The problem is: this menu disappears when a chart is selected. I can change it to "Chart menu bar" but then it disappears when no chart is selected. What do I have to put there to make the menu visible all the time? -- Fredrik E. Nilsen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
Add to both Worksheet menu Bar and Chart Menu Bar commandbars.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Fredrik E. Nilsen" wrote in message ... Ok, probably a stupid question but: I'm creating a custom menu with buttons to apply custom user defined charts: Dim myMenu As Object Set myMenu = CommandBars("Worksheet menu bar").Controls.Add(Type:=msoControlPopup) With myMenu .Caption = "User defined charts" . . . End With The problem is: this menu disappears when a chart is selected. I can change it to "Chart menu bar" but then it disappears when no chart is selected. What do I have to put there to make the menu visible all the time? -- Fredrik E. Nilsen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
On Mon, 12 Feb 2007 11:28:09 -0000, "Bob Phillips"
wrote: Add to both Worksheet menu Bar and Chart Menu Bar commandbars. I'm sorry but I don't know how to do that in the current macro. -- Fredrik E. Nilsen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
Repeat the code that you have changing Worksheet Menu bar to Chart Menu Bar
in the second instance of that code. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Fredrik E. Nilsen" wrote in message ... On Mon, 12 Feb 2007 11:28:09 -0000, "Bob Phillips" wrote: Add to both Worksheet menu Bar and Chart Menu Bar commandbars. I'm sorry but I don't know how to do that in the current macro. -- Fredrik E. Nilsen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
On Mon, 12 Feb 2007 13:03:06 -0000, "Bob Phillips"
wrote: Repeat the code that you have changing Worksheet Menu bar to Chart Menu Bar in the second instance of that code. Thanks, I figured out I could do it like that. But it seems strange to me if there is no method to achieve this in one statement? -- Fredrik E. Nilsen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
You need to add the menu to two menu bars, so it takes two statements, or a
loop. Dim myMenu As Object dim vMenuBars as variant dim iMenu as integer vMenuBars = Array("Worksheet Menu Bar", "Chart Menu Bar") For iMenu = 0 to 1 Set myMenu = CommandBars(vMenuBar(iMenu)).Controls.Add(Type:=ms oControlPopup) With myMenu .Caption = "User defined charts" ' etc. End With Next - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Fredrik E. Nilsen" wrote in message ... On Mon, 12 Feb 2007 13:03:06 -0000, "Bob Phillips" wrote: Repeat the code that you have changing Worksheet Menu bar to Chart Menu Bar in the second instance of that code. Thanks, I figured out I could do it like that. But it seems strange to me if there is no method to achieve this in one statement? -- Fredrik E. Nilsen |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
Why would it? They are separate commandbars applicable to different
circumstances, so it seems reasonable to me that you program both. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Fredrik E. Nilsen" wrote in message ... On Mon, 12 Feb 2007 13:03:06 -0000, "Bob Phillips" wrote: Repeat the code that you have changing Worksheet Menu bar to Chart Menu Bar in the second instance of that code. Thanks, I figured out I could do it like that. But it seems strange to me if there is no method to achieve this in one statement? -- Fredrik E. Nilsen |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
or...
Dim cBar as commandbar dim myMenu as CommandBarControl For Each cBar in Application.Commandbars If cBar.Type = msoBarTypeMenuBar then set myMenu = cBar.Controls.Add(Type:=msoControlPopup) ' etc. End If Next - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jon Peltier" wrote in message ... You need to add the menu to two menu bars, so it takes two statements, or a loop. Dim myMenu As Object dim vMenuBars as variant dim iMenu as integer vMenuBars = Array("Worksheet Menu Bar", "Chart Menu Bar") For iMenu = 0 to 1 Set myMenu = CommandBars(vMenuBar(iMenu)).Controls.Add(Type:=ms oControlPopup) With myMenu .Caption = "User defined charts" ' etc. End With Next - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Fredrik E. Nilsen" wrote in message ... On Mon, 12 Feb 2007 13:03:06 -0000, "Bob Phillips" wrote: Repeat the code that you have changing Worksheet Menu bar to Chart Menu Bar in the second instance of that code. Thanks, I figured out I could do it like that. But it seems strange to me if there is no method to achieve this in one statement? -- Fredrik E. Nilsen |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu disappears
On Mon, 12 Feb 2007 12:20:35 -0500, "Jon Peltier"
wrote: or... Dim cBar as commandbar dim myMenu as CommandBarControl For Each cBar in Application.Commandbars If cBar.Type = msoBarTypeMenuBar then set myMenu = cBar.Controls.Add(Type:=msoControlPopup) ' etc. End If Next As always: thank you very much for your help! -- Fredrik E. Nilsen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Editor Menu Bar and Toolbar disappears | Excel Discussion (Misc queries) | |||
menu toolbar disappears and right click wont work in charts | Charts and Charting in Excel | |||
Custom sort in macro disappears | Excel Programming | |||
addin and main menu disappears | Excel Programming | |||
Data Analysis Options disappears from |
Excel Discussion (Misc queries) |