Macro Path
 

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?

Macro Path
 

Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?

Macro Path
 

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?

Macro Path
 

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 wrote:

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?








--

Dave Peterson

Macro Path
 

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" wrote in message
...
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 wrote:

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?








--

Dave Peterson

Macro Path
 

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 wrote:

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" wrote in message
...
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 wrote:

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?








--

Dave Peterson

--

Dave Peterson

Macro Path
 

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

"Dave Peterson" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?








--

Dave Peterson

--

Dave Peterson

Macro Path
 

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?








--

Dave Peterson

--

Dave Peterson

Macro Path
 

Yep.

Derek Hart wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?








--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Macro Path
 

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


"Dave Peterson" wrote in message
...
Yep.

Derek Hart wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?








--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Macro Path
 

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

On Sun, 30 Dec 2007 09:55:51 -0800, "Derek Hart" wrote:

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


"Dave Peterson" wrote in message
...
Yep.

Derek Hart wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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?

"Jim Rech" wrote in message
...
Maybe you should state your exact problem.

--
Jim
"Derek Hart" wrote in message
...
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?








--

Dave Peterson

--

Dave Peterson

--

Dave Peterson