On Aug 20, 12:52*pm, OssieMac
wrote:
Hi again Darren,

My apologies for not getting back to you sooner but I was busy with other
things plus I have had to research a little to get your answer. However, if
you place the following code in ThisWorkbook module then it should do what
you initially asked for and open the Save As dialog box with the default Save
As set toMacro enabled.

I have put some explanation in comments but basically it tests for xl2007
and if not 2007 then exits. If it is xl2007 then it tests to see if already amacro enabled(xlsm) document and if so exits so that it does not annoy the
users by continually going into the SaveAs dialog box every time they save.

Feel free to get back to me again if not doing exactly what you want and
don't forget to make a backup of your document first in case you have a
problem.

If you want to disable it for any reason, then un-comment Exit Sub
immediately under the sub name and it will not process. Have also included
code to be run if it aborts and you need to re-enable alerts and events. (Can
do this from within the VBA editor.)

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

'Exit Sub * *'Un-comment to disable the macro.

'Test current Excel application version
If Val(Application.Version) < 12 Then
* * 'Not xl2007 then exit the sub and allow normal save
* * Exit Sub
End If

'Is xl2007 so further processing
Dim strThisWorkbook

Application.DisplayAlerts = False

Application.EnableEvents = False

'Assign current workbook name to a string variable
strThisWorkbook = ThisWorkbook.Name

If Right(strThisWorkbook, 5) = ".xlsm" Then
* * 'Already xlsm document so skip SaveAs DialogBox
* * GoTo Re_enable
End If

Cancel = True * 'Cancel Initial save

'Replace ".xls" with ".xlsm" in the filename
strThisWorkbook = Replace(strThisWorkbook, ".xls", ".xlsm")

'Open the Save As dialog box with the
'default Workbook name and Filetype
'Note: 52 replaces the FileType variable
'xlOpenXMLWorkbookMacroEnabled because this
'variable not recognized in earlier versions
'of Excel and causes compile problems if
'Option Explicit is enabled.
Application.Dialogs(xlDialogSaveAs) _
* * .Show strThisWorkbook, 52

Re_enable: *'Re-enable alerts and Events
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Sub Re_Enable_Events()
'If you get a failure in the code in the previous
'sub then Events and Display Alerts remain turned off.
'Run this macro to turn events back on.
'Can run it from the VBA Editor.
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Regards,

OssieMac

Ossie,

This worked briliiantly. Thank you so much for your time and effort in
supplying the code, and likewise the descriptive remarks. I have
solved a problem and learned some new commands in the process. Thanks
again.

Regards,
Darren