Apologies for posting again:

Ive tried the following 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 tha
there 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

you shouldn't be calling the save as method again as
you're already in it. Setting Cancel to True stops
further processing of the method. If cancel is set to
fale, then if its a SaveAs call, then application's
GetSaveFileName window will open normally - you don't
cvall it again.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel AsBoolean)
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
End If
End Sub

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----

Apologies for posting again:

Ive tried the following 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
.

HI Eddie,
Try this,

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
Cancel = True
MsgBox "There appears to be some missing or invalid data."
Else
With Application
.EnableEvents = False
Cancel = True
.Dialogs(xlDialogSaveAs).Show
.EnableEvents = True
End With
End If
End Sub


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for the fast response Patrick,

Ive tried that and it works fine. It would be nice though if the user
was still able to save the form, even though there are mistakes. But
to have the message make them aware of it first.

I was using the SaveAs because most people will open it from an email
and forget to save it to their network drives.

Many Thanks


------------------------------------------------
~~ 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