Enforcing Macro-enabled saves
 

Hi group.

I have an Excel workbook which contains VBA. It is used by some Excel
'03 and Excel '07 users.

I need to find a way to make the Excel '07 users save it as macro
enabled. I was thinking the right way to do that would be some code as
a Workbook_BeforeSave subroutine.

Could anyone suggest some code that I could insert into the
Workbook_BeforeSave subroutine to:

1. Firstly, check whether the user is using '07 (because if they
aren't it isn't an issue); and
2. If they are using '07, make the "Save As Type" drop-down box in the
resulting "Save As" dialog box show Macro-enabled workbook as a
default.

Any suggestions would be greatly appreciated. I have a working
knwoledge of VBA but some of these more advanced commands I'm still
learning.

Thanks,
Darren

Enforcing Macro-enabled saves
 

Hi Darren,

I wonder if I have interpreted your question correctly. If you have some
users using xl2003 and some using xl2007 with the same workbook, if it is
saved as an xl2007 macro enabled workbook then the xl2003 users will not be
able to open it.

Would it not be better to force the save in xl2003 format always and then
the macro will be usable by both xl2007 and xl2003 users?

The following code in ThisWorkbook module will achieve the above. However,
take careful note of the comments in the code. Also you may wish to delete
the line
Application.DisplayAlerts = False

NOTE: Make a backup copy of the workbook before copying this code into the
VBA editor.

Feel free to get back to me if this is not what you want.

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

'Suppress repeated calls to this sub
Application.EnableEvents = False

'Suppress the alert msgbox
Application.DisplayAlerts = False

Cancel = True 'cancels the initial save

'Following code saves as Excel 97-2003 type workbook
'Replace TestWorkbook.xls with your workbook name.

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\OssieMac\Documents\Excel\TestWorkbook.xl s", _
FileFormat:=xlExcel8, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

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.
'If this occurs, 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


"Darren" wrote:

Hi group.

I have an Excel workbook which contains VBA. It is used by some Excel
'03 and Excel '07 users.

I need to find a way to make the Excel '07 users save it as macro
enabled. I was thinking the right way to do that would be some code as
a Workbook_BeforeSave subroutine.

Could anyone suggest some code that I could insert into the
Workbook_BeforeSave subroutine to:

1. Firstly, check whether the user is using '07 (because if they
aren't it isn't an issue); and
2. If they are using '07, make the "Save As Type" drop-down box in the
resulting "Save As" dialog box show Macro-enabled workbook as a
default.

Any suggestions would be greatly appreciated. I have a working
knwoledge of VBA but some of these more advanced commands I'm still
learning.

Thanks,
Darren

Enforcing Macro-enabled saves
 

Hi Again Darren,

Error in the previous code. I tested it in xl2007 but not earlier version.
Change the line of code to Save as to the following.

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\OssieMac\Documents\Excel\TestWorkbook.xl s", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

FileFormat:=xlExcel8 is applicable to xl2007; not earlier versions
FileFormat:=xlNormal works with both
--
Regards,

OssieMac


"OssieMac" wrote:

Hi Darren,

I wonder if I have interpreted your question correctly. If you have some
users using xl2003 and some using xl2007 with the same workbook, if it is
saved as an xl2007 macro enabled workbook then the xl2003 users will not be
able to open it.

Would it not be better to force the save in xl2003 format always and then
the macro will be usable by both xl2007 and xl2003 users?

The following code in ThisWorkbook module will achieve the above. However,
take careful note of the comments in the code. Also you may wish to delete
the line
Application.DisplayAlerts = False

NOTE: Make a backup copy of the workbook before copying this code into the
VBA editor.

Feel free to get back to me if this is not what you want.

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

'Suppress repeated calls to this sub
Application.EnableEvents = False

'Suppress the alert msgbox
Application.DisplayAlerts = False

Cancel = True 'cancels the initial save

'Following code saves as Excel 97-2003 type workbook
'Replace TestWorkbook.xls with your workbook name.

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\OssieMac\Documents\Excel\TestWorkbook.xl s", _
FileFormat:=xlExcel8, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

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.
'If this occurs, 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


"Darren" wrote:

Hi group.

I have an Excel workbook which contains VBA. It is used by some Excel
'03 and Excel '07 users.

I need to find a way to make the Excel '07 users save it as macro
enabled. I was thinking the right way to do that would be some code as
a Workbook_BeforeSave subroutine.

Could anyone suggest some code that I could insert into the
Workbook_BeforeSave subroutine to:

1. Firstly, check whether the user is using '07 (because if they
aren't it isn't an issue); and
2. If they are using '07, make the "Save As Type" drop-down box in the
resulting "Save As" dialog box show Macro-enabled workbook as a
default.

Any suggestions would be greatly appreciated. I have a working
knwoledge of VBA but some of these more advanced commands I'm still
learning.

Thanks,
Darren

Enforcing Macro-enabled saves
 

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