Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO POP UP SAVE AS WINDOW
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO POP UP SAVE AS WINDOW
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 "K" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'Save as' window in VBA | Excel Discussion (Misc queries) | |||
how to save a desired window size but hv window comeup fullsz by d | Excel Discussion (Misc queries) | |||
Run Macro If Save Is Canceled On SaveAs Window In VBA | Excel Programming | |||
Run Macro If Save Is Canceled On SaveAs Window In VBA | Excel Programming | |||
How to call File Open / Save Window in a Macro | Excel Programming |