Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Excel '03] check-box menu items and cell context menus don't disp
Morning all,
I've been tasked with making some modifications to a VBA add-in for Excel 2003. Not my usual programming territory but so far so good except for one thing: In creating a short menu appended to the "Cell" Application.CommandBars and then, according to user input, setting the "State" property to either MsoButtonState.msoButtonDown or MsoButtonState.msoButtonUp I don't get any sort of visual indication of the menu item being selected or not. By contrast, the exact same code (just hooked onto the main menu bar instead of the 'Cell' context menu) functions correctly and I get the appropriate highlight and checkbox to show the menu item is/isn't selected. I can't find any similar occurences online and I'm a little stumped. I suppose the root cause is probably differences between the main menu and context menu objects - I'm treating them as the same for now (all Type:=msoControlButton and accessed via Office.CommandBarButton instances). It'd be greatly appreciated if someone could point me in the right direction for creating context menu entries with on/off visual indication :-) Thanks in advance, Jack |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Excel '03] check-box menu items and cell context menus don't disp
Hi Jack,
I don't quite follow what you're after but try the following for ideas Dim mbFlag As Boolean Const mTAG_2 As String = "TestTagBtn1" Sub TestAdd() CellMenu True End Sub Sub TestRemove() CellMenu False End Sub Sub CellMenu(bAdd As Boolean) Dim cPop As CommandBarPopup Dim cBtn As CommandBarButton Dim cBar As CommandBar Const TAG_1 As String = "TestTagPop" Set cBar = Application.CommandBars("Cell") On Error Resume Next Set cPop = Application.CommandBars.FindControl(Tag:=mTAG_1) On Error GoTo 0 If Not cPop Is Nothing Then cPop.Delete If bAdd Then Set cPop = cBar.Controls.add(msoControlPopup, temporary:=True) cPop.Caption = "Test menu" cPop.Tag = mTAG_1 Set cBtn = cPop.Controls.add(msoControlButton) With cBtn .Caption = "Macro1" .Style = msoButtonCaption .OnAction = "Macro1" .State = CLng(mbFlag) ' msoButtonDown/Up .Tag = mTAG_2 End With End If End Sub Sub Macro1() Dim cBtn As CommandBarButton mbFlag = Not mbFlag On Error Resume Next 'If called by the button Set cBtn = Application.CommandBars.ActionControl On Error GoTo 0 'or Set cBtn = Application.CommandBars.FindControl(Tag:=mTAG_2) If Not cBtn Is Nothing Then cBtn.State = CLng(mbFlag) End If MsgBox mbFlag End Sub Hopefully you will be able to toggle the tick aganst the Macro1 button on the rt-click Cell menu Regards, Peter T "Jack Hoxley [MVP]" <Jack Hoxley wrote in message ... Morning all, I've been tasked with making some modifications to a VBA add-in for Excel 2003. Not my usual programming territory but so far so good except for one thing: In creating a short menu appended to the "Cell" Application.CommandBars and then, according to user input, setting the "State" property to either MsoButtonState.msoButtonDown or MsoButtonState.msoButtonUp I don't get any sort of visual indication of the menu item being selected or not. By contrast, the exact same code (just hooked onto the main menu bar instead of the 'Cell' context menu) functions correctly and I get the appropriate highlight and checkbox to show the menu item is/isn't selected. I can't find any similar occurences online and I'm a little stumped. I suppose the root cause is probably differences between the main menu and context menu objects - I'm treating them as the same for now (all Type:=msoControlButton and accessed via Office.CommandBarButton instances). It'd be greatly appreciated if someone could point me in the right direction for creating context menu entries with on/off visual indication :-) Thanks in advance, Jack |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Excel '03] check-box menu items and cell context menus don't
Peter,
Thanks for the reply. I think your code snippet is close to what I want (I get an error about not finding 'Macro1' that I need to dig into) - I saw it create a [x] type check-box next to one of the menu items :-) To eleborate on what I was originally trying to describe... I have code like this in an Auto_Open() method: Dim cBar As CommandBar For Each cBar In Application.CommandBars If cBar.Controls.Parent.Name = "Cell" Then 'search for any existing 'MenuName' entries and remove them For Each oCtrl In cBar.Controls If oCtrl.Caption = MenuName Then oCtrl.Delete End If Next oCtrl ' Add the root of the context menu Set CtxMnuRoot = cBar.Controls.Add(Type:=msoControlPopup) CtxMnuRoot.Caption = MenuName ' .... This code inserts a "my menu" (or whatever) in the right-click context menu for a cell in Excel. Under the "CtxMenuRoot" object in the code above I 'hang' several other sub-menu items. These simply serve to control other parts of the plugin and are all on/off type boolean switches. Set btnLoggingOff = CtxMenuRoot.Controls.Add(Type:=msoControlButton) btnLoggingOff.Caption = LoggingOffId btnLoggingOff.Tag = LoggingOffId btnLoggingOff.OnAction = ThisWorkbook.Name & "!'ChangeLogging " & False & "'" The above being an example of one of the sub-menu items I hang off the context menu. There are 6 more fragments of code like this. In the referenced "ChangeLogging" macro function I have code like this (after the actual business logic): Set menu = CtxMenuRoot.Controls.Item(LoggingOffId) menu.State = MsoButtonState.msoButtonDown 'or 'ButtonUp' if appropriate Whilst my internal boolean tracking is working (the code is *definitely* being called) the UI doesn't update to show or hide the check-box mark to tell the user that they have/haven't enabled a feature. I can't find the combination of type/style/flag/method that allows me to show something like: +---------------------+ | [x] Logging Enabled | +---------------------+ or +---------------------+ | Logging Enabled | +---------------------+ menu items - its just the little [x] part thats causing problems :-) Any ideas what I might be missing?? Cheers, Jack |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Excel '03] check-box menu items and cell context menus don't
If you pasted all the code in my previous post and you are getting an error
about not being able to find Macro1 you may have another similarly named procedure. Either rename Macro1 as something unique (don't forget to update the OnAction property of the button) or better still qualify the macro with the workbook's name like this sOnAction = "'" & ThisWorkbook.Name & "'!Macro1" ..OnAction = sOnAction You should be able to call Macro1 with that button and toggle the button's state property. Thereafter hopefully you will be able to control your own button State properties. Afraid I changed something at the last moment before posting - In CellMenu() change each instance of "mTAG_1" to "TAG_1" Because of that the remove button code won't work. Just tidy up with this Application.CommandBars("Cell").Controls("Test Menu").Delete Regards, Peter T PS Looks like you need buttons on the both normal and print-view Cell menus "Jack Hoxley [MVP]" wrote in message ... Peter, Thanks for the reply. I think your code snippet is close to what I want (I get an error about not finding 'Macro1' that I need to dig into) - I saw it create a [x] type check-box next to one of the menu items :-) To eleborate on what I was originally trying to describe... I have code like this in an Auto_Open() method: Dim cBar As CommandBar For Each cBar In Application.CommandBars If cBar.Controls.Parent.Name = "Cell" Then 'search for any existing 'MenuName' entries and remove them For Each oCtrl In cBar.Controls If oCtrl.Caption = MenuName Then oCtrl.Delete End If Next oCtrl ' Add the root of the context menu Set CtxMnuRoot = cBar.Controls.Add(Type:=msoControlPopup) CtxMnuRoot.Caption = MenuName ' .... This code inserts a "my menu" (or whatever) in the right-click context menu for a cell in Excel. Under the "CtxMenuRoot" object in the code above I 'hang' several other sub-menu items. These simply serve to control other parts of the plugin and are all on/off type boolean switches. Set btnLoggingOff = CtxMenuRoot.Controls.Add(Type:=msoControlButton) btnLoggingOff.Caption = LoggingOffId btnLoggingOff.Tag = LoggingOffId btnLoggingOff.OnAction = ThisWorkbook.Name & "!'ChangeLogging " & False & "'" The above being an example of one of the sub-menu items I hang off the context menu. There are 6 more fragments of code like this. In the referenced "ChangeLogging" macro function I have code like this (after the actual business logic): Set menu = CtxMenuRoot.Controls.Item(LoggingOffId) menu.State = MsoButtonState.msoButtonDown 'or 'ButtonUp' if appropriate Whilst my internal boolean tracking is working (the code is *definitely* being called) the UI doesn't update to show or hide the check-box mark to tell the user that they have/haven't enabled a feature. I can't find the combination of type/style/flag/method that allows me to show something like: +---------------------+ | [x] Logging Enabled | +---------------------+ or +---------------------+ | Logging Enabled | +---------------------+ menu items - its just the little [x] part thats causing problems :-) Any ideas what I might be missing?? Cheers, Jack |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Excel '03] check-box menu items and cell context menus don't
"Peter T" wrote: If you pasted all the code in my previous post and you are getting an error about not being able to find Macro1 you may have another similarly named procedure. I haven't had time to get to the bottom of it, but I suspect this is the case. I'll look into it :) "Peter T" wrote: Because of that the remove button code won't work. Might well have worked too well - I've lost all of my menu's in Excel playing around with this code and variations of it :-) Gives me an interesting excuse for not doing any work if nothing else... "Sorry Boss, I can't do that" / "why not?" / "I have no menus anymore" ;-) Probably be Tues next week before I get to look into this again, thanks for the help! Jack |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Excel '03] check-box menu items and cell context menus don't
Because of that the remove button code won't work.
Might well have worked too well - I've lost all of my menu's in Excel playing around with this code and variations of it :-) That couldn't have occurred directly as a result of what I posted. Just in case I've caught you in time DO NOT close Excel. Find your *.xlb file, back it up. Now quit Excel, rename the new xlb and replace with the backup. The *xlb file stores your toolbars and menus, it's updated when you quit Excel. Regards, Peter T "Jack Hoxley [MVP]" wrote in message ... "Peter T" wrote: If you pasted all the code in my previous post and you are getting an error about not being able to find Macro1 you may have another similarly named procedure. I haven't had time to get to the bottom of it, but I suspect this is the case. I'll look into it :) "Peter T" wrote: Because of that the remove button code won't work. Might well have worked too well - I've lost all of my menu's in Excel playing around with this code and variations of it :-) Gives me an interesting excuse for not doing any work if nothing else... "Sorry Boss, I can't do that" / "why not?" / "I have no menus anymore" ;-) Probably be Tues next week before I get to look into this again, thanks for the help! Jack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add item to 'Cell' context menu with XML (RibbonX) | Excel Programming | |||
Updating context menu in Excel | Excel Discussion (Misc queries) | |||
Add items to context menu | Excel Worksheet Functions | |||
Context menu of Cell | Excel Programming | |||
Custum Excel RMB Context Menu | Excel Programming |