Excel 97 Macros refer to original file name

Hi rhomulos
If your macros are on a custom toolbar (Which is the best way) then try this

Sub Auto_Open
 Toolbars("YourToolbarName").Visible = True
end sub

Sub Auto_Close
 Toolbars("YourToolbarName").Delete
End sub

This way whenever the workbook is closed the full path will not be added to the macro.

whenever the workbook opens it will be available

The personal macro is a hidden sheet that runs in the background whenever you start the Application.
To view it go to Window>Unhide then you will see the personal macro worksheet then when you hit Alt+F11 you will see the Personal.xls VBA Project in the project window
Let me know if I understood you correctly
Antrat

sorry should of mentioned that you can also attatch the toolbar to the file by going to View>Toolbars>Customize>Attatch then select the Custom toolbar and click copy
Antrat

Hello,

I started with a blank Excel97 Sheet and create two Macros and a Custom Toolbar
The Macros were added to the custom toolbar and worked fine. I then added the following code as suggested by antrat (Toolbars didn't work I had to use Application.CommandBars)

Sub Auto_Open()
    Application.CommandBars("Custom1").Visible = True
End Sub

Sub Auto_Close()
    Application.CommandBars("Custom1").Visible = False
End Sub

Unfortunately although the Auto Open/Close worked fine it still record the macro as
'C:\directory\Filename.xls'!macro1 and when I change the C:\directory\filename.xls by moving or renaming the file the toolbar still looks for the macro in the old location. :-(

Matthew

rhomulos
Sorry about missing the Application bit.
Your Auto_Close macro must say
 
    Application.CommandBars("Custom1").Delete

Not visible=False

Antrat

If I delete it when I close down the file how will it be possible to make a non existent tool bar visible when I re open it.

I just had a test work by using Auto_Open to assign the macros to the buttons on the existing toolbar using

Application.CommandBars("Custom1").Controls.Item(1).OnAction = "Macro1"
Application.CommandBars("Custom1").Controls.Item(2).OnAction = "Macro2"

Since this reapplied the Macros to the buttons on the Toolbar at startup it was able to work even though I would change the file name.

THis is one workaround however how would a user normally record a macro, attach to a button or menu and then have it still work if the file was ever moved or renamed?  Their must be a better system then manually or programmatically reassinging macros all the time?????

rhomulos
I know the word Delete is not a nice one but what happens is this
Once the toolbar is attatched to the Workbook it will try to make a link to it whenever the file is closed Hence the full path name.
So when you put delete in your Auto_Close macro it wont delete the Toolbar altogether but rather detach it from the Workbook.

if you are not sure just save your file in a new directory or name then try it.

Antrat
 

But don't forget to remove the path name first from your macros before trying the Auto_Close and Open

If I delete it when I close down the file how will it be possible to make a non existent tool bar visible when I re open it.

I just had a test work by using Auto_Open to assign the macros to the buttons on the existing toolbar using

Application.CommandBars("Custom1").Controls.Item(1).OnAction = "Macro1"
Application.CommandBars("Custom1").Controls.Item(2).OnAction = "Macro2"

Since this reapplied the Macros to the buttons on the Toolbar at startup it was able to work even though I would change the file name.

THis is one workaround however how would a user normally record a macro, attach to a button or menu and then have it still work if the file was ever moved or renamed?  Their must be a better system then manually or programmatically reassinging macros all the time?????

Hello,

I have created the following code which allows me to change the filename and the location of my Excel SpreadSheet and still have the ToolBars/Macro's working everytime without User Intervention.

I'd like to know what systems other people have used in place of code like this?

**** Code Starts Here ****

Dim CustomToolBar
Dim MyTitle As String
Dim ButtonOne   As CommandBarButton
Dim ButtonTwo   As CommandBarButton
Dim ButtonThree As CommandBarButton
Dim ButtonFour  As CommandBarButton
'Automatically runs at startup
Sub Auto_Open()
    'Creates Toolbar and assigns macro's to it.
    Create_Toolbar
End Sub

Sub Auto_Close()
    'Deletes the Toolbar
    Application.CommandBars(MyTitle).Delete
End Sub


Sub Macro1()
    MsgBox ("Macro One")
End Sub

Sub Macro2()
    MsgBox ("Macro Two")
End Sub

Sub Macro3()
    MsgBox ("Macro Three")
End Sub

Sub Macro4()
    MsgBox ("Macro Four")
End Sub

'Creates ToolBar and creates four buttons and assigns four macros to them
Sub Create_Toolbar()

'Names the Command Bar
MyTitle = "Take Off"

'Creates the CommandBar
Application.CommandBars.Add(Name:=MyTitle).Visible = True

Set CustomToolBar = CommandBars(MyTitle)
'Adds the Buttons to the Tool Bar, Id:=1 means blank icon, number can be changed
Set ButtonOne = CustomToolBar.Controls.Add(Type:=msoControlButton, Id:=1)
Set ButtonTwo = CustomToolBar.Controls.Add(Type:=msoControlButton, Id:=1)
Set ButtonThree = CustomToolBar.Controls.Add(Type:=msoControlButton, Id:=1)
Set ButtonFour = CustomToolBar.Controls.Add(Type:=msoControlButton, Id:=1)
'
'Changes Button to a Caption Only Style and assigns it to Macro1
With ButtonOne
    .Style = msoButtonCaption
    .OnAction = "Macro1"
    .TooltipText = "Macro1 ToolTipText"
    .Caption = "Macro1 Caption Text"
End With

With ButtonTwo
    .Style = msoButtonCaption
    .OnAction = "Macro2"
    .TooltipText = "Macro2 ToolTipText"
    .Caption = "Macro2 Caption Text"
End With
With ButtonThree
    .Style = msoButtonCaption
    .OnAction = "Macro3"
    .TooltipText = "Macro3 ToolTipText"
    .Caption = "Macro3 Caption Text"
End With

With ButtonFour
    .Style = msoButtonCaption
    .OnAction = "Macro4"
    .TooltipText = "Macro4 ToolTipText"
    .Caption = "Macro4 Caption Text"
End With


End Sub


**** Code Finishes ****

Hi rhomulos
Was there somthing wrong with the examples I gave you?
I don't doubt that the code you have written works, but why not just create the the toolbar with the macros yourself and let the Auto_Open and Auto_Close macros do the rest.
They will still work if the user changes the file name and location.
How did you go with the personal macro workbook?
Antrat

If I create a worksheet with a custom toolbar in it that has buttons on it with Macro's assigned and I use a line of code in the Auto_Close which deletes that toolbar how can I then send the file to somebody else.

Once I've deleted the toolbar by closing down the file how will the next person be able to use it?  I read your point at detaching the toolbar but if the Toolbar is detached and/or deleted then how can I spread the file to other users?

Could you email me a excel file where this is already setup or cut n paste the code for it perhaps???

rhomulos
The custom Toolbars that are created are all stored in Excel5.XLB which is in the Windows directory. So custom Toolbars can be attached to XLS or XLA files and then distrubuted to any number of users.
When the person opens the file with the custom Toolbar attacthed the Toolbar will appear then when the user closes the file it wiil Automatically be added to their XLB file.
So next time the user open the file the Toolbar will be available.


Some other points that may be of use to you are that if you go into the VBE and select this workbook then Type: "Private Sub Workbook_NewSheet(ByVal sh As Object)" and this procedure place: If ActiveWork.ActiveSheet.Name <> "Your Sheetname here" Then
Toolbars("Your ToolBar here").Visible=False
Else
Toolbars("Your ToolBar here").Visible=True
End If
End sub
this way if they add another Worksheet your toolbar will not be visible.
You will also notice on the right in the Procedure Window you will now have loads of options to to play around with to make your Toolbar Visible when neccesary and not visible
at other times.
Antrat
P.S if still unsure post you Email address here and I'll send a dummy file with a toolbar attached.

I will try that out, it sounds interesting, please send dummy file to matthew@northnet.com.au

Matthew

I got the dummy file and it works the way you said it would although I cannot find a Excel5.xlb file on my machine, although I did find a mharris8.xlb file (are they related)

Anyway your toolbars("Toolbar Name").Delete certainly works well although it still seems like strange logic to me that it was required by microsoft to be that way.

How would a illiterate user create a toolbar and macro system that could be transported easily, would they too have to start writing VB code.  I can teach some of my smarter users how to do this but it increases the level of support and training they require. :-(

Please post an answer for me to accept.  Thank you.