The macro below works but... it’s a bit confusing for the end user in
that I does save the file name as A1 to the right drive / directory –
however it does show the end users it has done that.

It will also change it again if A1 is changed but its not clear to the
end user that the save has been done.

When the save as option is chosen in the file menu the save takes
place but in the Save As box it shows the previous file name etc.
Also if it’s a new sheet from a template it defaults to “Documents”
and I need it to default to the sheet in “my drive”

My approach to this problem might be all wrong.

What I want is to prevent the “end user” from messing up the original
sheet – but to be able to fill it in then save it with a name and file
number and leave the original un changed. Quite often they ignore the
“read only” use the form add the data and then save it over the
original before noticing what they have done.

I could use a FORM but the sheet is very complex (in the back ground)
and end users are OK with filling in their part of the sheet without
too much trouble – so a FORM is out.

NOTE – the end users are not very smart…

Thanks John

Thanks to Don Guillett for the macro below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)


mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=ms
End Sub

Hi John

Add theese two lines to your macro:

Cancel=True
ActiveWorkbook.Saved=True

I would also add a message box to inform user that the workbook has
been saved:

msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")

Regards,
Per

On 2 Okt., 19:55, Johnnyboy5 wrote:
The macro below works but... it’s a bit confusing for the end user in
that I does save the file name as A1 to the right drive / directory –
however it does show the end users it has done that.

It will also change it again if A1 is changed but its not clear to the
end user that the save has been done.

When the save as option is chosen in the file menu *the save takes
place but in the Save As box it shows the previous file name etc.
Also *if it’s a new sheet from a template it defaults to “Documents”
and I need it to default to the sheet in “my drive”

My approach to this problem might be all wrong.

What I want is to prevent the “end user” from messing up the original
sheet – but to be able to fill it in then save it with a name and file
number and leave the original un changed. *Quite often they ignore the
“read only” use the form add the data and then save it over the
original before noticing what they have done.

I could use a FORM but the sheet is very complex (in the back ground)
and end users are OK with filling in their part of the sheet without
too much trouble – so a FORM is out.

NOTE – the end users are not very smart…

Thanks *John

Thanks to Don Guillett *for the macro below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=ms
End Sub

On 2 Oct, 20:43, Per Jessen wrote:
Hi John

Add theese two lines to your macro:

Cancel=True
ActiveWorkbook.Saved=True

I would also add a message box to inform user that the workbook has
been saved:

msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")

Regards,
Per

On 2 Okt., 19:55, Johnnyboy5 wrote:



The macro below works but... it’s a bit confusing for the end user in
that I does save the file name as A1 to the right drive / directory –
however it does show the end users it has done that.

It will also change it again if A1 is changed but its not clear to the
end user that the save has been done.

When the save as option is chosen in the file menu *the save takes
place but in the Save As box it shows the previous file name etc.
Also *if it’s a new sheet from a template it defaults to “Documents”
and I need it to default to the sheet in “my drive”

My approach to this problem might be all wrong.

What I want is to prevent the “end user” from messing up the original
sheet – but to be able to fill it in then save it with a name and file
number and leave the original un changed. *Quite often they ignore the
“read only” use the form add the data and then save it over the
original before noticing what they have done.

I could use a FORM but the sheet is very complex (in the back ground)
and end users are OK with filling in their part of the sheet without
too much trouble – so a FORM is out.

NOTE – the end users are not very smart…

Thanks *John

Thanks to Don Guillett *for the macro below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=ms
End Sub

Thanks Per nearly there.

one issue - the very top of the Excel screen on the left hand side
still shows the original name.

john

On 2 Oct, 20:43, Per Jessen wrote:
Hi John

Add theese two lines to your macro:

Cancel=True
ActiveWorkbook.Saved=True

I would also add a message box to inform user that the workbook has
been saved:

msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")

Regards,
Per

On 2 Okt., 19:55, Johnnyboy5 wrote:



The macro below works but... it’s a bit confusing for the end user in
that I does save the file name as A1 to the right drive / directory –
however it does show the end users it has done that.

It will also change it again if A1 is changed but its not clear to the
end user that the save has been done.

When the save as option is chosen in the file menu *the save takes
place but in the Save As box it shows the previous file name etc.
Also *if it’s a new sheet from a template it defaults to “Documents”
and I need it to default to the sheet in “my drive”

My approach to this problem might be all wrong.

What I want is to prevent the “end user” from messing up the original
sheet – but to be able to fill it in then save it with a name and file
number and leave the original un changed. *Quite often they ignore the
“read only” use the form add the data and then save it over the
original before noticing what they have done.

I could use a FORM but the sheet is very complex (in the back ground)
and end users are OK with filling in their part of the sheet without
too much trouble – so a FORM is out.

NOTE – the end users are not very smart…

Thanks *John

Thanks to Don Guillett *for the macro below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=ms
End Sub

Oh. how can i save it as a template - as the macro runs before I can
save it as a template

John

John

Why don't you use a template (xlt) workbook in the first place?

Then when the user opens it they will actually create a new workbook
based on the template.

They can then do whatever the need/want with the new workbook without
affecting the (original) template.

On 3 Oct, 08:23, norie wrote:
John

Why don't you use a template (xlt) workbook in the first place?

Then when the user opens it they will actually create a new workbook
based on the template.

They can then do whatever the need/want with the new workbook without
affecting the (original) template.

Hi

because if they use a template its default save is to "Documents" and
I need the results saved to a different drive / folder. I am trying
to avoid them having to use a macro and that all the change of drive
stuff etc takes place in the back ground. Any ideas ?

John