Excel worksheet macro - Changing Name of File causes ERRORS

[TeriM]

Hi all, I am hoping someone can assist me.
I have created an Excel spreadsheet with about 10 macros, which are stored IN the excel spreadsheet. It is to be used by many individuals as a desktop item, which means the macros need to be stored inside it (most users are clueless and there is a toolbar with macro shortcuts)

BIG PROBLEM
I want to make the XLS I created an XLT, yet the macros can no longer be found because the original xls name was changed to xlt. How do I change WHERE the Macro looks for the file (I also want to change the name because it is too long)

Can anyone advise?

 

[AMCCoder]

There are a couple of assumptions I will make in this post, so please forgive me if I assume too much and fail to answer your question. I also know nothing of your level of expertise; again, please forgive me if my explanation is a little verbose.

My guess is that you created a toolbar - not to be confused with a menu (as in on the Menu Bar after Help) - in which to store your macro shortcuts in your original .xls workbook. There is a way to attach this to your workbook so that it goes whereever your workbook does. The only obstacle is that your toolbar should be removed from Excel when you close the workbook. Here's what I have done in circumstances similar to the one I described above.

Attach your toolbar to the workbook.
1. After creating your toolbar (or even after creating it), make sure you note the name of your toolbar, you'll need it later. It might be something like "User Macros." We'll call that name ToolBar_Name.
2. Right-click on any existing Toolbar and choose Customize....
3. Click Attach...
4. Click on the name of your toolbar, and choose Copy.
5. Your macro toolbar will now go where your spreadsheet does.

You should remove your toolbar when closing the workbook. The attached toolbar will copy to the Excel Application, and unless you remove it, it will remain available even when your workbook is not open.

Here's what I have done. This uses the auto_close macro (this macro is run by Excel when you close the workbook). There are better functions to use when doing this (I am hoping one of our more experienced programmers might be able to help with this), but auto_close works fine with me. You can copy this code directly on the same page on which your macros are stored (in the Visual Basic Editor).

Where ToolBar_Name is the name of your toolbar...

Sub auto_close()
    Dim bar As CommandBar
    For Each bar In Application.CommandBars
        If bar.Name = "ToolBar_Name" Then
            bar.Delete
        End If
    Next bar
End Sub

I hope this information helps you with your question.
Best Regards,
Joseph