Recreate Excel 2003 Menus in Excel 365

[jrjobe]

Hello All!

I am trying to update an Excel 2003 file/files with custom macros/toolbars to work in the latest version of Excel. These files were built back in the 90s and updated to work on Excel 2003. I volunteered to take on the task of updating these files but am running into a problem figuring out how to load or recreate a version of the toolbar in Excel 365. I know the toolbar itself, excel11.xlb, won't load, but what can I do to get a similar function of these menus to work in later versions of Excel?

Here's a snapshot of the toolbar:

Here's how all this works in Excel 2003:
1. User opens BLANK ESTIMATE1.xls
2. Save As and Rename to "test4.xls", the file name can be any name
3. User then clicks on Est Name in the toolbar (see pic above)
3.1 Clicking "Est Name" opens DATABASE.xls along with the VBA Editor and Module 1 and the user changes the name in the code to the same file name then closes or minimizes the VB Editor window:

Sub BACK()

'NOTE: CHANGE ("") TO "FILE NAME"
'                  BELOW
  
  Windows("test4.xls").Activate

'   Application.Run "back"  note were sheets("estimate") is at
'   Sheets("Estimate").Select

End Sub

4. From here, the user can now add line items on the test4.xls worksheet utilizing the toolbar, which depending on the selection, will either copy from the Database.xls file and paste all associated entries based on the code in module 1 or take the user to the defined NAME in the database.xls file to select what they need and either use "alt-A" or press the toolbar button "Copy to Est Alt A".

For example, if the user needs to add some Raceway, they can drill down the menu above and select the type and size they need:

This particular example will copy four line items from the database.xls file and paste it into the test4.xls file, based on this code:

Sub EMTS_l()
'
' EMTS_1 Macro
' Macro recorded 4/30/2003
'
Windows("DATABASE.XLSM").Activate
'       EMT     COUP  CONN    support    label
Range("a4:G4,a15:G15,A48:G48,a672:G672,a685:G685").Select
Selection.COPY
Application.Run "BACK"
ActiveCell.Offset(0, 0).Range("a1:G1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "=+RC[-2]*RC[-1]"
Selection.COPY
ActiveCell.Offset(1, 0).Range("A1:A4").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 2).Select
ActiveCell.FormulaR1C1 = "=+RC[-4]*RC[-1]"
Selection.COPY
ActiveCell.Offset(1, 0).Range("A1:A4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, -4).Select
ActiveCell.FormulaR1C1 = "=+R[-1]c*0.1"
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=@round((+R[-3]C/8),0)"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=@round((+R[-4]C/25),0)"
ActiveCell.Offset(1, -1).Select

End Sub

Which then ends up on the test4.xls sheet as follows:

Clicking back on the database.xls file will show those four items highlighted on the sheet.

There aren't any formulas on either the test4.xls BASE worksheet or the database.xls LIST worksheet that I've been able to find, so everything seems to be performed based on the VBA in module 1 of the database.xls file. I plan on updating this and possibly trying something entirely different to get the same results, but for now, being able to execute this particular setup in later versions of Excel would buy me some time to create something better.

Here are some more of the VBA that may be applicable:

Sub RUNMACRO()
'
' RUNMACRO Macro
' Macro recorded 4/30/2003
'
'THIS RUNS A SUB MACRO INTO ANOTHER
'Application.Run "MACRO-NAME"

End Sub

Sub VALUE()
'
' VALUE Macro
' Macro recorded 6/12/2003
    'ActiveCell.Offset(0, 0).Range("A1").Select
    Selection.COPY
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

Thank you all for the help!

Oh! Another item of note, I have resaved both of the files above as database.xlsm and BLANK ESTIMATE1.xlsx, and further, I went through and updated all references to database.xls to database.xlsm in module 1...just trying to figure out how to utilize them now.

 

[jrjobe]

Okay, so right after I posted this, I figured out how to work with the new Ribbon layout and was able to add an "Estimate" tab along with adding the CHANGENAME Macro, naming it "Est Name" and performing the similar steps above, but with the xlsx file extension, I clicked on "Est Name" in the toolbar and it opened up the database.xlsm along with the VBA editor, allowing me to change the name to test.xlsx in the macro to match the new file name.

Now I need to figure out how to add the rest of the buttons, especially the ones with the additional drop-down menus.

 

[jrjobe]

After a lot more digging and changing search terms, I came across the website below that walks through creating a menu in Excel 2010+ using "Office RibbonX Editor". So far, I have been able to recreate the first menu tree for the database file. At this point, the only thing I will need to figure out is how to assign the macros to the appropriate entry. And of course, after opening and closing this document an X amount of times, the add-ins tab finally appeared along with the original menu that displays in Excel 2003. I'll still be working on updating to the new menu as I know it will at least be there, regardless...again, assuming I can figure out how to assign macros to the entries that need them.

Here's the website that got me going in the right direction:
How to Build a Custom Ribbon in Excel