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

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