how do I prevent a workbook from being saved?

[Guest]

At a certain point in my macro, the User can make a decision to create a new
workbook (called "Investment Quotation" as shown below). A small portion of
the macro is shown, below. At that point, the macro performs a
copy-and-paste from the current workbook to a new workbook. When that
happens, I need to prevent the original (first) workbook from being saved. (I
won't know the name of the first workbook.)

The problem is that I want the User to have the right to save/change the
original workbook. It's just that I'm trying to prevent them from saving the
original workbook ONLY if they decide to create a new workbook.

So, I guess my question is: how do I tell Excel to "don't allow a save" to
the first workbook? (Again, the User is allowed to save the file BEFORE
this part of the macro is executed.)

Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Investment Quotation"

 

[Guest]

One way I think would work:

After the paste is done add the following code lines:

ThisWorkbook.Activate
ThisWorkbook.Close SaveChanges:=False

This would cause the "Original" to be destroyed, thus solving your dilemma.
You could put a warning message for the user warning them that once they
elect to create a new book, the "Original" will no longer be available...

HTH.

 

[Guest]

Hi,
not sure i get it completely, but if you want the users to save a copy of
the current state of an open book, you can do a SaveCopyAs:
- say book1.xls is the currently open book
- do a SaveCopyAs into book2.xls
- book1 is still open in the current state (not saved yet) and a copy of the
current state has been saved to the closed book book2.xls
Would that work for you?

'--------------------------------------------------
Sub SaveACopy()
Dim wkb As Workbook
Dim f As Variant

Set wkb = ActiveWorkbook '<------- Active Book

'ask user to save a Copy
f = Application.GetSaveAsFilename("New Book.xls", "Excel File (*.xls),
*.xls", , "Save a Copy")
If f <> "False" Then 'user did not click Cancel button --> save copy
wkb.SaveCopyAs f
Else
MsgBox "SaveCopy cancelled by user."
End If

End Sub
'---------------------------------------

 

[Bob Phillips]

Maybe you could set its Saved property to true, and close it


Set this = ActiveWorkbook
Cells.Copy
Workbooks.Add
ActiveSheet.Paste
Sheets("Sheet1").Name = "Investment Quotation"
this.Saved = True
this.Close

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks, everyone, for the suggestions. I'll try 'em all but it sounds like
Quartz's is what I want. Thanks, again.
Dan