MACRO TO POP UP SAVE AS WINDOW

[K]

Hi all, I have excel 2007 file which is read only so no one can make
changes when using it. I have put a little note in every sheet of
that file which says that "save the file after making changes by going
on Save As and select Excel Macro-Enabled Workbook format" but lots
of users forget it sometime. So I want macro that when someone open
that file then "Save As" window should appear and in "File name" box
the text should show that "enter your file name" and in "Save as type"
box it should be appearing "Excel Macro-Enabled Workbook" and also it
should save the file in "Document" folder on "C" drive. But the main
thing is that one that macro should work when someone open the file or
Workbook and two that this macro should not be transfered to the "Save
As" file as I dont want macro to bring up "Save As" window when
someone open the Save As file. I only want this macro in the read
only file. For example if my read only file name is "XX" and someone
open this file the "Save As" window should appear and after when user
save the file and give name to that file lets say "ZZ" and now when
user open the file "ZZ" again i dont want macro to bring up "Save As"
window, in other words that Save As macro should be only work in
original read only or "XX" file but once its been Save as to different
place then macro should be deleted in those file or should not work.
I hope I was able to explain my question. Please can any friend can
help as this macro will save lots of time and will make things easy.

 

[Bernie Deitrick]

K,

Put this into the codemodule of the ThisWorkbook object: of course, change the XX to the actual
name...

Private Sub Workbook_Open()
If ThisWorkbook.Name <> "XX.xlsm" Then Exit Sub
ChDir "C:\Document"
ThisWorkbook.SaveAs Application.GetSaveAsFilename _
("New file name.xlsm", "Macro-enabled workbook files (*.xlsm), *.xlsm)", _
, "Enter Save As File Name"), 52
End Sub

HTH,
Bernie
MS Excel MVP