Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed! VB Code to "Save As..."
Actually, this is a little more clear. You can keep the screen updating.
Switch your sheets before the If and switch them back after the End if. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False If SaveAsUI Then Application.Dialogs(xlDialogSaveAs).Show Else ThisWorkbook.Save End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Elkar" wrote in message ... I have a workbook that I am requiring Macros to be activated in order to use. I've managed to do this by using the following code in the BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False ActiveWorkbook.Unprotect ("password") Worksheets("Warning").Visible = True Worksheets("Time Sheet").Visible = False Worksheets("Data Sheet").Visible = False ActiveWorkbook.Protect ("password") ActiveWorkbook.Save Application.EnableEvents = True ActiveWorkbook.Unprotect ("password") Worksheets("Time Sheet").Visible = True Worksheets("Data Sheet").Visible = True Worksheets("Warning").Visible = False ActiveWorkbook.Protect ("password") Application.ScreenUpdating = True ThisWorkbook.Saved = True End Sub This works great, except that the user can't use "Save As..." to change the filename. Even if I use the line Activeworkbook.SaveAs, it still only saves over the original filename. How can I modify this so that Save As will work? Any help is much appreciated. Thanks in advance! Elkar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed! VB Code to "Save As..."
Excellent!!! This is exactly what I needed. Thank you so much!!
Elkar "Jon Peltier" wrote: Actually, this is a little more clear. You can keep the screen updating. Switch your sheets before the If and switch them back after the End if. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False If SaveAsUI Then Application.Dialogs(xlDialogSaveAs).Show Else ThisWorkbook.Save End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Elkar" wrote in message ... I have a workbook that I am requiring Macros to be activated in order to use. I've managed to do this by using the following code in the BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False ActiveWorkbook.Unprotect ("password") Worksheets("Warning").Visible = True Worksheets("Time Sheet").Visible = False Worksheets("Data Sheet").Visible = False ActiveWorkbook.Protect ("password") ActiveWorkbook.Save Application.EnableEvents = True ActiveWorkbook.Unprotect ("password") Worksheets("Time Sheet").Visible = True Worksheets("Data Sheet").Visible = True Worksheets("Warning").Visible = False ActiveWorkbook.Protect ("password") Application.ScreenUpdating = True ThisWorkbook.Saved = True End Sub This works great, except that the user can't use "Save As..." to change the filename. Even if I use the line Activeworkbook.SaveAs, it still only saves over the original filename. How can I modify this so that Save As will work? Any help is much appreciated. Thanks in advance! Elkar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
Help Needed! VB Code to "Save As..." | Excel Programming | |||
save and restore "Workbook Menu Bar" & "Cell" menus | Excel Programming |