[Excel '03] check-box menu items and cell context menus don't disp

[Jack Hoxley [MVP]]

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

 

[Peter T]

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]]

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

 

[Peter 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

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

 

[Peter 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