P
Paul
As titled. Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Debra Dalgleish
You can create a new default template, with the 'Always save backup'
setting turned on:
Open a new workbook.
Choose File>Save As
From the Save As Type dropdown, choose Template (*.xlt)
Name the File "Book.xlt"
At the top right of the Save As dialog box, click the Tools button
Choose General Options
Check 'Always create backup', click OK
Save the file in the XLStart folder
(usually in Program Files/Microsoft Office/Office)
When you click the New button on the toolbar, the new workbook will be
based on this template.
A backup copy will be created when you save the file. (It doesn't appear
to create a copy when the file is originally saved, but creates one on
subsequent saves.)
setting turned on:
Open a new workbook.
Choose File>Save As
From the Save As Type dropdown, choose Template (*.xlt)
Name the File "Book.xlt"
At the top right of the Save As dialog box, click the Tools button
Choose General Options
Check 'Always create backup', click OK
Save the file in the XLStart folder
(usually in Program Files/Microsoft Office/Office)
When you click the New button on the toolbar, the new workbook will be
based on this template.
A backup copy will be created when you save the file. (It doesn't appear
to create a copy when the file is originally saved, but creates one on
subsequent saves.)
G
Gord Dibben
Paul
With VBA macro.
Sub BUandSave2()
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:="E:\GordStuff\Backup\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub
Gord Dibben Excel MVP
With VBA macro.
Sub BUandSave2()
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:="E:\GordStuff\Backup\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub
Gord Dibben Excel MVP
P
Paul
Thank you all. I have made it.
Debra Dalgleish said:You can create a new default template, with the 'Always save backup'
setting turned on:
Open a new workbook.
Choose File>Save As
From the Save As Type dropdown, choose Template (*.xlt)
Name the File "Book.xlt"
At the top right of the Save As dialog box, click the Tools button
Choose General Options
Check 'Always create backup', click OK
Save the file in the XLStart folder
(usually in Program Files/Microsoft Office/Office)
When you click the New button on the toolbar, the new workbook will be
based on this template.
A backup copy will be created when you save the file. (It doesn't appear
to create a copy when the file is originally saved, but creates one on
subsequent saves.)
J
Jack Sons
Gord,
I hava tried backing up like this with my most important (all inportant)
workbook that has many macros that are assigned to buttons. Problem is, when
I save the workbook in another directory - let alone in another partition -
the next time I open the workbook from its proper directory and I want to
execute a macro with a button, I always get the message that that macro
can't be found and I have to assign all macros again to their respective
buttons. How to overcome these drawbacks?
Jack Sons
The Netherlands
I hava tried backing up like this with my most important (all inportant)
workbook that has many macros that are assigned to buttons. Problem is, when
I save the workbook in another directory - let alone in another partition -
the next time I open the workbook from its proper directory and I want to
execute a macro with a button, I always get the message that that macro
can't be found and I have to assign all macros again to their respective
buttons. How to overcome these drawbacks?
Jack Sons
The Netherlands
G
Gord Dibben
Jack
Have a read at this google search thread for discussion of this subject by
David McRitchie and Bernie Dietrick and some code to repair the path.
http://snipurl.com/5xdf
Gord
Have a read at this google search thread for discussion of this subject by
David McRitchie and Bernie Dietrick and some code to repair the path.
http://snipurl.com/5xdf
Gord
J
Jack Sons
Gord,
Thank you, I read the thread but it is not really simple to conclude what to
do.
I am more or less in the dark.
Jack.
Thank you, I read the thread but it is not really simple to conclude what to
do.
I am more or less in the dark.
Jack.
Gord Dibben said:Jack
Have a read at this google search thread for discussion of this subject by
David McRitchie and Bernie Dietrick and some code to repair the path.
http://snipurl.com/5xdf
Gord
Gord,
I hava tried backing up like this with my most important (all inportant)
workbook that has many macros that are assigned to buttons. Problem is, when
I save the workbook in another directory - let alone in another partition -
the next time I open the workbook from its proper directory and I want to
execute a macro with a button, I always get the message that that macro
can't be found and I have to assign all macros again to their respective
buttons. How to overcome these drawbacks?
Jack Sons
The Netherlands
Gord Dibben said:Paul
With VBA macro.
Sub BUandSave2()
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:="E:\GordStuff\Backup\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub
Gord Dibben Excel MVP
G
Gord Dibben
Jack
That makes two of us<g>
Perhaps someone else will jump in with some suggestion/fix.
Gord
That makes two of us<g>
Perhaps someone else will jump in with some suggestion/fix.
Gord
Gord,
Thank you, I read the thread but it is not really simple to conclude what to
do.
I am more or less in the dark.
Jack.
Gord Dibben said:Jack
Have a read at this google search thread for discussion of this subject by
David McRitchie and Bernie Dietrick and some code to repair the path.
http://snipurl.com/5xdf
Gord
D
Dave Peterson
What kind of button do you use?
If you use a command button from the control toolbox toolbar, then the code
would exist in the _click procedure and would travel nicely.
If it's from the Forms toolbar, you could reassign the macro when you open the
workbook:
Option Explicit
Sub auto_open()
Worksheets("sheet1").Buttons("button 1").OnAction _
= ThisWorkbook.Name & "!test01"
End Sub
If you use a command button from the control toolbox toolbar, then the code
would exist in the _click procedure and would travel nicely.
If it's from the Forms toolbar, you could reassign the macro when you open the
workbook:
Option Explicit
Sub auto_open()
Worksheets("sheet1").Buttons("button 1").OnAction _
= ThisWorkbook.Name & "!test01"
End Sub
Jack said:Gord,
Thank you, I read the thread but it is not really simple to conclude what to
do.
I am more or less in the dark.
Jack.
Gord Dibben said:Jack
Have a read at this google search thread for discussion of this subject by
David McRitchie and Bernie Dietrick and some code to repair the path.
http://snipurl.com/5xdf
Gord
J
Jack Sons
Dave,
button I right click on the toolbar, edit>command>macros> and then I drag
teh smiley button to the toolbar, edit its surface, right click on it,
change name and caption and assign the proper macro.
So I created the buttons myself, they are in the two main toolbars of Excel
(don't know their English names, the topmost ones of the drop down menu). So
they are not fixed to the worksheet but always in the toolbars no matter
what workbook may be active. Some buttons correspond with macros of
personal.xls, which is never saved anywhere else and thus never have to be
reassigned, but other buttons correspond with modules of my main worksheet.
I can't copy that workbook to any other location than its present one
because when I later open that workbook from its regular location the
buttons will look for their macros elsewhere and I get the message that the
macros can't be found. I have to assign the macros from that workbooks
modules again to the buttons, as if it were for the first time.
I don't understand in the first place what rules decide where a button looks
for its macro. I also don't understand why it looks for the macro in the new
directory when the workbook is copied there, even when it is later opened
from its "own" (=regular) directory. What is the use of that behavior?
Why do the buttons not look for macros in the directory from which the
workbook is opened (with its modules in which those macros are), that would
be logical, or not?
I still do not understand how to use code to make the buttons of a workbook
look for their macro's in the directory from which that workbook was opened.
If that could be done, the problems would be gone (I hope and think).
Jack.
I don't now what is mentioned, I use the Dutch version of Excel. To make athe control toolbox toolbar ... the Forms toolbar
button I right click on the toolbar, edit>command>macros> and then I drag
teh smiley button to the toolbar, edit its surface, right click on it,
change name and caption and assign the proper macro.
So I created the buttons myself, they are in the two main toolbars of Excel
(don't know their English names, the topmost ones of the drop down menu). So
they are not fixed to the worksheet but always in the toolbars no matter
what workbook may be active. Some buttons correspond with macros of
personal.xls, which is never saved anywhere else and thus never have to be
reassigned, but other buttons correspond with modules of my main worksheet.
I can't copy that workbook to any other location than its present one
because when I later open that workbook from its regular location the
buttons will look for their macros elsewhere and I get the message that the
macros can't be found. I have to assign the macros from that workbooks
modules again to the buttons, as if it were for the first time.
I don't understand in the first place what rules decide where a button looks
for its macro. I also don't understand why it looks for the macro in the new
directory when the workbook is copied there, even when it is later opened
from its "own" (=regular) directory. What is the use of that behavior?
Why do the buttons not look for macros in the directory from which the
workbook is opened (with its modules in which those macros are), that would
be logical, or not?
I still do not understand how to use code to make the buttons of a workbook
look for their macro's in the directory from which that workbook was opened.
If that could be done, the problems would be gone (I hope and think).
Jack.
Dave Peterson said:What kind of button do you use?
If you use a command button from the control toolbox toolbar, then the code
would exist in the _click procedure and would travel nicely.
If it's from the Forms toolbar, you could reassign the macro when you open the
workbook:
Option Explicit
Sub auto_open()
Worksheets("sheet1").Buttons("button 1").OnAction _
= ThisWorkbook.Name & "!test01"
End Sub
Jack said:Gord,
Thank you, I read the thread but it is not really simple to conclude what to
do.
I am more or less in the dark.
Jack.
Gord Dibben said:Jack
Have a read at this google search thread for discussion of this subject by
David McRitchie and Bernie Dietrick and some code to repair the path.
http://snipurl.com/5xdf
Gord
D
Dave Peterson
First, the button you're using is on a commandbar. (I was questioning if the
button was placed on a worksheet from either the Forms toolbar or ControlToolbox
toolbar--View|toolbars will show you these.)
This sounds like a pretty generic macro (to make backups).
I think I'd separate it out from all (except one) workbook and keep it one
(personal.xl*???). You can accumulate all your macros into that one workbook
and just open that one.
You could attach that toolbar to that workbook so that it travels with the
workbook. You'll want to read Jan Karel Pieterse's notes if you go this
route--to avoid potential problems:
http://google.com/[email protected]
Jan Karel has a note on how to delete that toolbar when you close the file.
Then when you open the copy of the workbook, your toolbars won't have to compete
with each other (if one is already there by that name, then xl knows it doesn't
have to refresh it--bad for you!)
And I'd create a separate toolbar (each with a unique name) for each workbook.
But I think I'd use John Walkenbach's MenuMaker program for accessing these
types of common macros.
http://j-walk.com/ss/excel/tips/tip53.htm
You can build the menu when that workbook opens. It looks really neat and it's
pretty easy to modify.
button was placed on a worksheet from either the Forms toolbar or ControlToolbox
toolbar--View|toolbars will show you these.)
This sounds like a pretty generic macro (to make backups).
I think I'd separate it out from all (except one) workbook and keep it one
(personal.xl*???). You can accumulate all your macros into that one workbook
and just open that one.
You could attach that toolbar to that workbook so that it travels with the
workbook. You'll want to read Jan Karel Pieterse's notes if you go this
route--to avoid potential problems:
http://google.com/[email protected]
Jan Karel has a note on how to delete that toolbar when you close the file.
Then when you open the copy of the workbook, your toolbars won't have to compete
with each other (if one is already there by that name, then xl knows it doesn't
have to refresh it--bad for you!)
And I'd create a separate toolbar (each with a unique name) for each workbook.
But I think I'd use John Walkenbach's MenuMaker program for accessing these
types of common macros.
http://j-walk.com/ss/excel/tips/tip53.htm
You can build the menu when that workbook opens. It looks really neat and it's
pretty easy to modify.
Jack said:Dave,
I don't now what is mentioned, I use the Dutch version of Excel. To make athe control toolbox toolbar ... the Forms toolbar
button I right click on the toolbar, edit>command>macros> and then I drag
teh smiley button to the toolbar, edit its surface, right click on it,
change name and caption and assign the proper macro.
So I created the buttons myself, they are in the two main toolbars of Excel
(don't know their English names, the topmost ones of the drop down menu). So
they are not fixed to the worksheet but always in the toolbars no matter
what workbook may be active. Some buttons correspond with macros of
personal.xls, which is never saved anywhere else and thus never have to be
reassigned, but other buttons correspond with modules of my main worksheet.
I can't copy that workbook to any other location than its present one
because when I later open that workbook from its regular location the
buttons will look for their macros elsewhere and I get the message that the
macros can't be found. I have to assign the macros from that workbooks
modules again to the buttons, as if it were for the first time.
I don't understand in the first place what rules decide where a button looks
for its macro. I also don't understand why it looks for the macro in the new
directory when the workbook is copied there, even when it is later opened
from its "own" (=regular) directory. What is the use of that behavior?
Why do the buttons not look for macros in the directory from which the
workbook is opened (with its modules in which those macros are), that would
be logical, or not?
I still do not understand how to use code to make the buttons of a workbook
look for their macro's in the directory from which that workbook was opened.
If that could be done, the problems would be gone (I hope and think).
Jack.
Dave Peterson said:What kind of button do you use?
If you use a command button from the control toolbox toolbar, then the code
would exist in the _click procedure and would travel nicely.
If it's from the Forms toolbar, you could reassign the macro when you open the
workbook:
Option Explicit
Sub auto_open()
Worksheets("sheet1").Buttons("button 1").OnAction _
= ThisWorkbook.Name & "!test01"
End Sub