Disabling Saveas and saving file with a specific name only

[Hari]

Hi,

Can one disable "Save as" option in the file menu.

I have a file which will be filled with data and mailed back to me.
I want that the users save the file with a specific name only.

Im using a Macro to save a file where today's date is attached along
with the normal file name.

But I notice that this works only if the user presses save on the
file menu.

If they use Save as option then the user ends up saving file once in
my format and then s/he is prompted with a save as dialogue where s/he may
save it with a different name also.

Please tell me if there is a way one may overcome this.

Here is my present code which is pasted in "This workbook".


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

' Here I have code to check for data internal data consistency. The file
will not save if there is any problem.

If soandso = suchandsuch Then
Cancel = true
End if

ActiveWorkbook.SaveAs Filename:="C:\Hari " & Format(Now, "yyyy-mm-dd"),
FileFormat:=xlNormal

End Sub

Regards,
Hari
India

 

[Ron de Bruin]

One way if you use a English Excel version
Copy this in the Thisworkbook module.

Remember this is only working if you enabled macro's
when you open the workbook

Private Sub Workbook_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls("File") _
..Controls("Save &As...").Enabled = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Worksheet Menu Bar").Controls("File") _
..Controls("Save &As...").Enabled = True
End Sub

 

[Hari]

Hi Ron,

Thanx a lot for the code ( I have English version only). I will try it out
in a short while( presently in midst of some editing)

A doubt. U say that this will not work if the user does not enable macro.

Does that mean in the event of not enabling macros even the code
"Workbook_BeforeSave" will also not work.

Im concerned because I was thinking the user might send me inconsistent data
by unchecking macros.

If so, please tell me if there is a way to "force" the sub
Workbook_BeforeSave to be run ( when the user presses on save button )
irregardless of whether macros are enabled or not.

Regards,
Hari
India

 

[Ron de Bruin]

Hi Hari

If the user disable macro's when opening your workbook none of
the events will run.

If the user have protection set to high your events not run also.
There is no way to be sure that your code will run.