Recreate Excel 2003 Menus in Excel 365

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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:
1635790067086.png


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:

VBA Code:
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:
1635800961108.png


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:

VBA 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:
1635801655685.png


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:

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

End Sub

VBA Code:
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,575
Messages
5,765,188
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top