20 Ton Squirrel
New Member
- Joined
- Aug 18, 2011
- Messages
- 11
While customizing the right-click menu for cells, I am unable to get a CommandBarComboBox object to show up. Other controls populate fine and no errors are thrown. It is as though the code for adding the combobox is ignored completely.
My code is below, any guidance would be greatly appreciated!
My code is below, any guidance would be greatly appreciated!
Code:
'===============================================
' THISWORKBOOK
Option Explicit
Private Sub Workbook_SheetBeforeRightClick(ByVal wks As Object, ByVal rng As Range, booCancel As Boolean)
menuShowTEST
End Sub
Code:
'===============================================
' MODULE
Option Explicit
Public Const cMENU_TAG As String = "DS"
Private Sub menuInitializeTEST()
Dim cbcMain As CommandBarControl
Set cbcMain = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup, before:=1, temporary:=True)
With cbcMain
.Tag = cMENU_TAG
.Caption = "Report &Filters"
.BeginGroup = True
End With
With cbcMain.Controls.Add(Type:=msoControlComboBox, temporary:=True)
.Tag = cMENU_TAG
.Caption = "Meow"
.TooltipText = "Why isn't this showing up?"
.AddItem "element1"
.AddItem "element2"
.AddItem "element3"
.DropDownLines = 3
.ListIndex = 1
End With
With cbcMain.Controls.Add(Type:=msoControlButton, temporary:=True)
.Tag = cMENU_TAG
.Caption = "Apply"
.TooltipText = "Apply selected filters to data."
.OnAction = "menuFilterApply"
.BeginGroup = True
End With
With cbcMain.Controls.Add(Type:=msoControlButton, temporary:=True)
.Tag = cMENU_TAG
.Caption = "Remove"
.TooltipText = "Removes filters from data."
.OnAction = "menuFilterRemove"
.BeginGroup = False
End With
End Sub
Public Sub menuShowTEST()
menuRemoveTEST
menuInitializeTEST
End Sub
Public Sub menuRemoveTEST()
Dim cbc As CommandBarControl
On Error Resume Next
For Each cbc In Application.CommandBars.FindControls(Tag:=cMENU_TAG)
cbc.Delete
Next
On Error GoTo 0
End Sub