Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modifying Ron DeBruin's menu macro
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("M enuSheet"). _ 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("M enuSheet"). _ 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
modifying Ron DeBruin's menu macro
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... 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("M enuSheet"). _ 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 Sub ----------------------------------------------------------------------------------------------- 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("M enuSheet"). _ 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modifying Data in Drop Down Menu | Excel Worksheet Functions | |||
Ron DeBruin's Merge Worksheets Example | Excel Worksheet Functions | |||
Ron DeBruin's Sendmail | Excel Discussion (Misc queries) | |||
Modifying Macro | Excel Worksheet Functions | |||
Modifying the right-click popup menu of a shape | Excel Programming |