Saving a spreadsheet

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

Is there a way to set up a spreadsheet so that when you
click save, the name is saved as an entry of a particular
cell, and not saving any changes to the original
spreadsheet that the data was entered in? Thanks if anyone
can help.
 
Mike, give this macro a try, will save the workbook with the name in A1

Sub SaveAsSub()
Dim MyName As String
MyName = Range("A1").Value & ".xls"
ActiveWorkbook.SaveAs FileName:=MyName
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Mike,

If you want this to happen when automatically when the user uses ordinary
save commands (Ctrl-S, or the Save button, etc.), use this in the
ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True ' cancels original save (to current file name)
Application.EnableEvents = False ' keeps upcoming save from
' re-triggering this sub
Application.DisplayAlerts = False ' prevents "file already exists"
' message if saving again
ActiveWorkbook.SaveAs FileName:=Range("A1") ' save the file
Application.DisplayAlerts = True ' resets DisplayAlerts
Application.EnableEvents = True
End Sub

Note that the user won't be able to use Save As, as this will trump it.
 
Back
Top