VBA CHANGENAME

[jrjobe]

Hello!

I apologize upfront for the lengthy post but I am trying my best to be clear about what I am trying to do.

I have two macro-enabled workbooks that work with each other. These are being rebuilt from Excel 2003 when you were able to simply use .xls file extension for macro-enabled workbooks. The way the original workbooks operated, was when you opened one (without macros), you could click on a button and it would pull up the database.xls file (with macros) and the VBA editor automatically opened to change the name of the first workbook. The old format used a custom menu that will only display via the add-ins tab in later versions of Excel, but, they don't work in later versions - so I am rebuilding using the customUI.

Example:
Old file format:
Open BLANK ESTIMATE.xls
Save As to a new name (such as a company you are providing an estimate for)
Click on Estimate Name (macro button) that is tied to database.xls
--this opens the database.xls file along with the VBA editor to module 1 BACK() macro to change the name to reflect the new estimate name.
Doing the above seamlessly allowed both files to work together allowing the user to add certain parts/pricing from the database.xls file to the new estimate.xls

New file format with a customUI Ribbon tab (called Estimates):
I found that I had to have macros in both files in order for the new toolbar tab to work, so I saved both files as macro-enabled workbooks.
The difference here is that rather than having both workbooks have the exact same customUI Estimate tab, I removed ones not applicable to the other workbook. For example, if I wanted to add 1/2" PVC to the estimate, that menu item was not needed on the database.xlsm file and likewise, blank estimate.xlsm (or new estimate filename) doesn't need the "Copy to Est." button when individual items are added to the estimate. Just to be clear, the database.xlsm file has a LIST worksheet with NAME references for the multitude of categories we use to generate custom quotes for customers. The LIST worksheet is where the estimates.xlsm file gets the various parts and pricing from using various buttons on the customUI tab.

But, I have now run into a bit of a problem that I'm trying to resolve:
Both files now require the name change in the VBA

Here's the code I'm referring to:

The code is the same for both database.xlsm and BLANK ESTIMATE.xlsm

Module 1:

Sub BACK()

'NOTE: CHANGE ("") TO "FILE NAME"
'                  BELOW
    
  Windows("BLANK ESTIMATE.XLSM").Activate

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

End Sub

Module 2:

Sub CHANGENAME(control As IRibbonControl)
'added "control As IRibbonControl" to get the button to work in Excel 2007+
'Modified on 11/2/2021
' CHANGENAME Macro
' Macro recorded 4/30/2003
    Windows("DATABASE.XLSM").Activate
    'Application.Run "BACK"
    Application.Goto Reference:="BACK"
    
End Sub

The problem is when the user clicks on the "Estimate Name" button (on either workbook), it only pulls up the module 1 BACK() macro in database.xlsm where the user must change the name to the new estimate file name. If they don't, there is no way to pull parts and pricing over from database.xlsm. However, the name also needs to be changed in module 1 BACK() macro of the new estimate.xlsm, but I can't figure out a way to make it seamless for the user to change the name. I considered updating module 2 CHANGENAME() macro of the BLANK ESTIMATE.xlsm to reference BLANK ESTIMATE.xlsm instead of database.xlsm, however, doing so, would then break the code when the user saves the estimate to a new file name, unless there is a way to automatically change the name. This is what I need to overcome.

I know this might be a bit confusing, but please let me know if I need to clarify anything.

 

[jrjobe]

Alright, looks like I figured it out. After doing more updates, I ended up opening up the macro dialog box (for something else) and noticed that when I had both workbooks open, the box listed macros for both. For instance, I opened up the macro box from the estimate workbook and it showed the macros in the database as DATABASE.xlsm!macroname or DATABASE.xlsm!Module1.macroname. So I decided to see what that would do by updating the macro name in the customUI, and disabled the above macro that I asked about in the estimate workbook and everything seems to be working.

I am now going to work backward to see if I can change all macros back to the database file and change the estimate workbook to .xlsx instead of macro-enabled. So far it doesn't really seem that I need macros in the estimate file for the toolbar to display...but I'll know for certain as I slowly remove all macros back and delete them from the estimate file as I test it.