PDA

View Full Version : Any way to prevent users from saving the file?



maytey
09-05-2008, 09:56 AM
Hi,

I am currently preparing my company's budget and would require a macro for my budget templates. I will actually need a macro to check a sum of ranges to make sure it is not above a certain amount.

For example

Row B12 - Total Staff Cost - Maximum cap = $50K
Row B15 - Total G&A Cost - Maximum cap = $10K

Is there a way to stop the user from saving the file if the amount they have keyed in the summary file exceeds the budget guideline given?

Thanks.. Your help will be greatly appreciated!

CreganTur
09-05-2008, 11:30 AM
You could put your check in the BeforeSave Event of the workbook.

maytey
09-06-2008, 09:04 AM
How do i place this BeforeSave Event ?

mdmackillop
09-06-2008, 11:37 AM
Like this.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets(1)
If .Range("B12") > 50000 Or .Range("B15") > 10000 Then
Cancel = True
MsgBox "Not saved - Limits exceeded", vbCritical
End If
End With
End Sub