Custom Menu, Edit problem on submenus, .Add(msoControlEdit)

[stakar]

Hi there!
I have some kind of problem with a custom menu. I want to have a menu which i can edit a number (sheet1 A2 cell) and then to have an action if i change this number. My problem is that i cant get the number on the edit menu box in the menu

Here is the following code and look at the menu Choose Draws which is empty
-----------------------------------------------------------
Option Explicit

Dim cbMenu As CommandBarControl
Dim cbSubMenu As CommandBarControl
Public MenuSeries
Public DrawsChoice As CommandBarComboBox
Private Const DefaultSelection As String = "16"

Sub CreateMenu()
' creates a new menu.
' can also be used to create commandbarbuttons
' may be automatically executed from an Autpen macro or a Workbook_Open eventmacro
RemoveMenu ' delete the menu if it already exists
' create a new menu on an existing commandbar (the next 6 lines)
Set cbMenu = Application.CommandBars(1).Controls.Add(msoControlPopup, , , , True)
With cbMenu
.Caption = "&Joker"
.Tag = "JokerTag"
.BeginGroup = False
End With
' or add to an existing menu (use the next line instead of the previous 6 lines)
'Set cbMenu = Application.CommandBars.FindControl(, 30007) ' Tools-menu
If cbMenu Is Nothing Then Exit Sub ' didn't find the menu...

' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Menu Item1"
.OnAction = ThisWorkbook.Name & "!Macroname"
.Delete
End With

'--------------------------------------------------------------'
' add a submenu to the submenu
Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&User's Functions"
.Tag = "SubMenu3"
.BeginGroup = True
End With

With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "Choose Function Ctrl+E"
.OnAction = "ChooseFormulas"
End With

With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "Create Function Ctrl+D"
.OnAction = "CreateFormulas"
End With

'--------------------------------------------------------------'
'Problem Part
'--------------------------------------------------------------'

' add a submenu to the submenu
Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&Choose Draws"
.Tag = "SubMenu2"
End With
'---
Set DrawsChoice = cbSubMenu.Controls.Add(msoControlEdit)
With DrawsChoice
.Caption = "::"
.OnAction = "Draws_Selection"

If Len(Worksheets("sheet1").[A2]) = 0 Then
If .Text = "" Then .Text = DefaultSelection
Worksheets("sheet1").[A2] = .Text
Else
.Text = Worksheets("sheet1").[A2]
End If
End With

'--------------------------------------------------------------'
' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Remove this menu"
.OnAction = ThisWorkbook.Name & "!RemoveMenu"
.Style = msoButtonIconAndCaption
.FaceId = 463
.BeginGroup = True
.Delete
End With
Set cbSubMenu = Nothing
Set cbMenu = Nothing
End Sub

Sub RemoveMenu()
' may be automatically executed from an Auto_Close macro or a Workbook_BeforeClose eventmacro
DeleteCustomCommandBarControl "JokerTag" ' deletes the new menu
End Sub

Private Sub DeleteCustomCommandBarControl(CustomControlTag As String)
' deletes ALL occurences of commandbar controls with a tag = CustomControlTag
On Error Resume Next
Do
Application.CommandBars.FindControl(, , CustomControlTag, False).Delete
Loop Until Application.CommandBars.FindControl(, , CustomControlTag, False) Is Nothing
On Error GoTo 0
End Sub
------------------------------------------

If someone can help me i ll appreciate this
Thanks in advance

 

[stakar]

I just want to mention that the above problem part working for excel 2000 but now im using excel 2007. Maybe this is why its not appearing the code.
Can someone look at it???
I ll appreciate

 

[stakar]

Noone can help me with this??
Its a kind of important because a whole procedure depends on that
At least run the code ....

 

[stakar]

Noone can help me with this??
Its a kind of important because a whole procedure depends on that
At least run the code ....

This was before excel 2007. It was working perfect. Now its not appears
If someone can help me with this, im stuck



Uploaded with ImageShack.us