I would like to stop users from saving a workbook if a
cells contents have not been completed, I know that I can
use the Event BeforeSave to do a test on the contents of a
cell but how can I stop the save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
If Range("C10") = "" Then
MsgBox "Please complete cell C10"
Exit Sub
End If
End Sub

Set the Cancel argument to True. E.g.,


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
If Range("C10") = "" Then
MsgBox "Please complete cell C10"
Cancel = True
Exit Sub
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"newboy18" wrote in message
...
I would like to stop users from saving a workbook if a
cells contents have not been completed, I know that I can
use the Event BeforeSave to do a test on the contents of a
cell but how can I stop the save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
If Range("C10") = "" Then
MsgBox "Please complete cell C10"
Exit Sub
End If
End Sub

Thanks Chip and Frank
Cancel = True

Works just fine

Apologies for ressurrecting an old topic.

Ive tried the previous technique for stopping a save when there are
blank cells. Unfortunately due to the nature of the data Iam
requesting, it is not appropriate for me to use the Exit Sub if it has
not been correctly filled in.

Therefore Iam using much the same but as a prompt on saving that there
are gaps in the data. It would appear though that Iam getting the
msgbox twice.

Presumably this is because after Cancel=True Iam calling the SaveAs
box, any ideas how I might get round this?

Code below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim Blanks As Range
Dim SumBlanks As Double

Set Blanks = Range("aj63:aj263")
SumBlanks = Application.Sum(Blanks)
If SumBlanks 0 Then
MsgBox "There appears to be some missing or invalid data."
Cancel = True
'Exit Sub
End If
Application.Dialogs(xlDialogSaveAs).Show
End Sub


Many thanks,
Eddie


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Apologies for ressurrecting an old topic.

Ive tried the previous technique for stopping a save when there ar
blank cells. Unfortunately due to the nature of the data Ia
requesting, it is not appropriate for me to use the Exit Sub if it ha
not been correctly filled in.

Therefore Iam using much the same but as a prompt on saving that ther
are gaps in the data. It would appear though that Iam getting th
msgbox twice.

Presumably this is because after Cancel=True Iam calling the SaveA
box, any ideas how I might get round this?

Code below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean)
Dim Blanks As Range
Dim SumBlanks As Double

Set Blanks = Range("aj63:aj263")
SumBlanks = Application.Sum(Blanks)
If SumBlanks 0 Then
MsgBox "There appears to be some missing or invalid data."
Cancel = True
'Exit Sub
End If
Application.Dialogs(xlDialogSaveAs).Show
End Sub


Many thanks,
Eddi

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Just set Cancel = true after the message box.

(From the online help:

Syntax
Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)
SaveAsUi True if the Save As dialog box will be displayed.
Cancel False when the event occurs. If the event procedure sets this
argument to True, the workbook isn't saved when the procedure is finished.)

--
HTH -

-Frank Isaacs
Dolphin Technology Corp.
http://vbapro.com





"newboy18" wrote in message
...
I would like to stop users from saving a workbook if a
cells contents have not been completed, I know that I can
use the Event BeforeSave to do a test on the contents of a
cell but how can I stop the save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
If Range("C10") = "" Then
MsgBox "Please complete cell C10"
Exit Sub
End If
End Sub