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

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

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