Macro Path

[Derek Hart]

I built some macros and thought if I moved my Excel file to another
directory, the same code would be found because it is inside the Excel file.
Is there some setting to tell Excel to use the relative path to find the
macros, inside its own file?

 

[Jim Rech]

Maybe you should state your exact problem.

 

[Derek Hart]

I built macros that are called by a toolbar button. Works perfectly. Move
the Excel file to another directory. The macros can't be located, because
the file was moved, but the macros are in the darn file. Make sense?

 

[Dave Peterson]

You may find your life becomes much easier by using code to do this work.

You can include code to create the toolbar when the workbook is opened and
include code to destroy the toolbar when the workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm

 

[Derek Hart]

Let me try again. I have written VBA code in an Excel spreadsheet called
MySheet.xls which is in c:\temp - My code is in a module called Module1, the
default module. I then create a toolbar by hand, and Assign the Macro (not
a recorded macro, but my code in Module1) to toolbar buttons. Works
perfectly. Then move my file from c:\temp to c:\NewDir - click the toolbar
button and get the error "The macro c:\temp\MySheet.xls!TestMacro cannot be
found." I just want the toolbar button to reference THIS file. How do I do
that?

 

[Dave Peterson]

Right click on each icon and reassign the macro to the button. When/if you move
or share this with others, you could have the same problem.

Or build the toolbar on the fly.

 

[Derek Hart]

There is no way to built a toolbar, and refer to code within the file, no
matter where the file moves?

 

[Derek Hart]

Dave,

In your code you have this line:

..OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)

Will that handle this issue, and always assign it to the path the file
happens to be in?

Derek

 

[Dave Peterson]

Yep.

Dave,

In your code you have this line:

.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)

Will that handle this issue, and always assign it to the path the file
happens to be in?

Derek

 

[Derek Hart]

Dave,

Great code. One more question. The code that runs on the Auto_Close event
removes the toolbars. This event runs if I edit data, click to exit the
spreadsheet, get asked if I want to save the document, and then if I hit
cancel, I am still in the spreadsheet but the code ran to remove the
toolbars. Any way to have a true Auto_Exit event, or to bring back the
toolbars if the user hits cancel. If not, do you have a workaround?

Derek

 

[Gord Dibben]

Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveWorkbook
If Not .Saved Then
msg = "Do You Want Save Changes to "
msg = msg & .Name & "?"
ans = MsgBox(msg, vbQuestion + vbYesNoCancel)
Select Case ans
Case vbYes
.Save
Case vbNo
.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
End With
Call deletetoolbarsmacro
End Sub


Gord Dibben MS Excel MVP

Dave,

Great code. One more question. The code that runs on the Auto_Close event
removes the toolbars. This event runs if I edit data, click to exit the
spreadsheet, get asked if I want to save the document, and then if I hit
cancel, I am still in the spreadsheet but the code ran to remove the
toolbars. Any way to have a true Auto_Exit event, or to bring back the
toolbars if the user hits cancel. If not, do you have a workaround?

Derek