Solved: Prevent save as .xlsx

[CaptRon]

Is there a way to prevent a workbook from being saved in the .xlsx format?

I've had 3 employees already save a .xls workbook to .xlsx and obliterate the VBA project - all the code is deleted. They either ignore the warning dialog boxes or just don't understand the significance of the message.

I have the workbook save on close now with this:
[vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Hide Sheets on close to require macros be enabled when workbook opened
Call HwkstCO15
With Application
.DisplayAlerts = False
ThisWorkbook.Saved = True
.DisplayAlerts = True
End With
End Sub[/vba]

The HwkstCO15 macro hides all but one sheet and concludes with:
[vba]With ThisWorkbook
.Protect , Structure:=True, Windows:=False
.Save
End With[/vba]

Thanks,

Ron

[SamT]

BeforeSave and BeforeClose Events

[vba]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)
'This sub prevents anyone from saving the book and immediately closes it.
SaveAsUI = False 'Don't show the Save As dialog
Cancel = True 'Don't complete the save
ME.Close SaveChanges:=False 'Close without saving
End Sub
[/vba]

[vba]
Private Sub Workbook_BeforeClose(Cancel as Boolean)
Cancel = True 'Stop the previous close procedure
SaveAsUI = False
ME.Saved = True 'Make me think I'm saved.
ME.Close SaveChanges:=False
End Sub
[/vba]

Not tested. LAte at night. Probably errors.

Those two should force a close without Save on; Save, Save AS and Close, while preventing any changes from being saved.

You'll have to comment these out if you modify the book and want to save it.

You can throw in a check for Application.Version and allow save if 2003 to let anyone on the right version save it.

SamT

[Bob Phillips]

The proper way to handle this is separate the code from the data. Put the code in an addin that opens and processes the data workbook. That way, they never touch the code workbook.

[CaptRon]

I believe I have put together a solution that will work for me as a result of your excellent help and the example I found here on Ron de Bruin's site:
http://www.rondebruin.nl/saveas.htm

Rather than offer the user the option of saving as .xls or .xlsm, I chose to restrict the save to .xls.

Clicking on Save or SaveAs brings up a userform that directs the save procedure beyond that point.

Thanks again for your assistance.

Ron

[CaptRon]

I found I needed to specify a path or Excel would save to the default location so I added that provision in this workbook.

Ron

[CaptRon]

I decided to allow the user the option to save as either .xls or .xlsm depending on their version of Excel.

Ron

[CaptRon]

xld,

I have been exploring your suggestion regarding the use of add-ins to house the code outside of the workbook. I don't have much experience with add-ins.

I think I see the value though, especially with Excel 2007. If the code is contained in a companion add-in, then the user can save the workbook as an .xlsx without any negative repercussions.

The downside, it seems to me, is that each user must have the workbook and the companion add-in to achieve full functionality. One w/o the other won't work.

I have a question. Is it possible to build an add-in that contains code work for several different workbooks or is it a better practice to have each workbook matched with a unique and corresponding add-in?

Thanks,

Ron

[Bob Phillips]

Ron,

That is true to an extent, but in practice, the data workbook would be saved centrally, and everyone would access it from there. Your addin could control it so that if someone else has it open, it won't open it.

In my design modles, I would say that one addin should serve one business objective. That business objective might be (should be) managing many functions, and that might be many functions on one data workbook, or many functions on many data workbooks. If they all relate to the same business objective, put it all in one addin.

The big benefit is that you can update the code in an addin without touching the data, better development abstraction.

I also build my addins in 2003 with commandbars, but have a 'lite' 2007 addin which gets opend in 2007 so that I can have a ribbon rather than those menus in the Addins tab.

[Matt Lucas]

This was perfect for me. Thank you.