Hi.

I have a workbook that is on a shared drive. Is there anyway that I
can program it to save on to desktop on close? (No matter who uses it,
I want it to autmatically save a copy onto their desktop when they
close it - without asking them if they want to).


Thanx

On 22 Sep, 15:19, "J.W. Aldridge" wrote:
Hi.

I have a workbook that is on a shared drive. Is there anyway that I
can program it to save on to desktop on close? (No matter who uses it,
I want it to autmatically save a copy onto their desktop when they
close it - without asking them if they want to).

Thanx

Hi,

You will need to put something in the before close event.

So this isn't in a standard module but in the "Thisworkbook" module,
then select workbook, before close (use the drop downs at the top of
the screen).

As the desktop location changes with the different versions of the
operating system you will need to shell out to get the location, the
following code should do the trick:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set WshShell = CreateObject("WScript.Shell")
ThisWorkbook.SaveAs Filename:=WshShell.SpecialFolders("Desktop") &
"\" & ThisWorkbook.Name
End Sub

James