VBA CHANGENAME

jrjobe

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

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jrjobe

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,045
Messages
5,767,810
Members
425,437
Latest member
blaix

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