Stopping a macro to edit the template then re-saving the macro for use

[Johnnyboy5]

Hi

I am nearly there - for what I need anyway.

The macro below doesn't allow me to edit the sheet (template sheet)
and re-save it as a "template" it will only save as a file.

How to I stop the macro from running the save function in order that I
can save it as a template - but at the same time know that when the
template is used it will run the macro.

Johnnny






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







' Place the current files path and filename in the titlebar:
Windows(1).Caption = ActiveWorkbook.FullName



' Place your own application name in the titlebar:
Application.Caption = "SPICE SHEET FOLDER"





Cancel = True
ActiveWorkbook.Saved = True
msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")
Application.DisplayFullScreen = False
End Sub

 

[Don Guillett Excel MVP]

Hi

I am nearly there - for what I need anyway.

The macro below doesn't allow me to edit the sheet (template sheet)
and re-save it as a  "template" it will only save as a file.

How to I stop the macro from running the save function in order that I
can save it as a template - but at the same time know that when the
template is used it will run the macro.

Johnnny

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

' Place the current files path and filename in the titlebar:
      Windows(1).Caption = ActiveWorkbook.FullName

      ' Place your own application name in the titlebar:
      Application.Caption = "SPICE SHEET FOLDER"

Cancel = True
ActiveWorkbook.Saved = True
msg = MsgBox("The workbook has been saved as " & ms, vbInformation +
vbOKOnly, "Save As")
Application.DisplayFullScreen = False
End Sub

You could have had your saveAS assigned to a button/shape. The way you
have it, just comment out (put an apostophe) in front of the macro to
NOT fire it. Save and then UN comment to go back to where you are now.

 

[Johnnyboy5]

You could have had your saveAS assigned to a button/shape. The way you
have it, just comment out (put an apostophe) in front of the macro to
NOT fire it. Save and then UN comment to go back to where you are now.

Thanks - tried that but when I save the template it saves the macro
with the apostophe it doesnt run the macro again when I want to use
the template to create a new record. When I then remove the apostophe
again the macro wont let me save it as a template.

I dont really want the end user to use any buttons etc, I would like
it all just to happen in the back ground.

regards

John

 

[Don Guillett Excel MVP]

Thanks  - tried that but when I save the template it saves the macro
with the apostophe it doesnt run the macro again when I want to use
the template to create a new record.  When I then remove the apostophe
again the macro wont let me save it as a template.

I dont really want the end user to use any buttons etc,  I would like
it all just to happen in the back ground.

regards

John- Hide quoted text -

- Show quoted text -

"People in hell want ice water"
You could have an inputbox message asking what you want to do but,
again, this requires effort on your part

 

[Johnnyboy5]

"People in hell want ice water"
You could have an inputbox message asking what you want to do but,
again, this requires effort on your part

Mmm fair comment...