modifying Ron DeBruin's menu macro

[Guest]

I recently obtained a copy of Ron's QAT menu macro and it's been working
great.
One of my colleagues has further modified it, and gave me a copy of his
modifications/macros.
These changes make 5 menus on the QAT (not including my own version).
Initially, all looked in order, but after I did the hide and save command
Ron built in to the xlsb file I found that my personally modified macro menu
disappeared, and only 4 of my colleague's remained, with no direct
correlation to the specified menu. I.e., it apepars that any duplicated
language deleted tertiary menus' contents, and only left one set active.
So, my question is:
What changes do I need to make in the general code of Ron's MenuCode macro
to allow for the others, without compromising the integrity of the others?
I see where in the MenuCode module there is code checks for, and then
deletes a duplicate menu.
I've tried changing the code that calls the MenuSheet, to be calling
MenuSheetX but that has not resolved it. I then changed the name of the menu
at the top of the xlsb file, for level one. That too did not work. I then
tried another workbook.xlsb file, nor did that work. I then tried each of
those together, and nothing has worked thus far.

Ron's code for the menu is below
----------------------------------------------------------------------------------

Sub CreatePopUp()
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, MacroName, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
Call RemovePopUp

' Initialize the row counter
Row = 5

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

' First we have create a PopUp menu with the name of the value in B2
With Application.CommandBars.Add(ThisWorkbook.Sheets("MenuSheet"). _
Range("B2").Value, msoBarPopup, False,
True)

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
MacroName = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = .Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem = .Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = ThisWorkbook.Name & "!" & MacroName
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem =
MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = ThisWorkbook.Name & "!" & MacroName
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End With
End Su
-----------------------------------------------------------------------------------------------
My specific changes were
1- the macro name from CreatePopUp to CreatePopUpX, where X is a variable
that changes with each new menu.
2- I did not change this element: "Set MenuSheet =
ThisWorkbook.Sheets("MenuSheet")". In fact, I just now noticed it.
3- I did change this next element: " With
Application.CommandBars.Add(ThisWorkbook.Sheets("MenuSheet"). _
Range("B2").Value, msoBarPopup, False,
True)"
The change that I did was from MenuSheet to MenuSheetX, again where X is a
variable that changes for each new worksheet, or workbook.
4- nor have I changed the following: " Dim MenuSheet As Worksheet"
Thinking about this further, I'll assume that each of the "MenuSheet" for
each need to match. that much does make sense.
If I've missed something else, would someone please clarify it for me?
Thanks in advance.
Best to all.

 

[Ron de Bruin]

I think he use the same popup menu name
But you have already try another name for your popup menu.

You can send me the other workbook private and i will look at it for you