Validating Workbook?

  • Thread starter Thread starter FJB0623
  • Start date Start date
F

FJB0623

Is it possible to validate a workbook, i.e. if all of the unprotected cells in
the workbook (several worksheets) are not completed, the workbook would not
save and/or would mark the offending cells?

I use a workbook which contains over 400 lines required to set up a new client
in our system. Many of the those lines must be completed for each individual
client. The majority of times the workbooks are not completed with all of the
required fields. I would like some way to "force" the user to complete all
required fields.

Thanks
To reply to this message, remove "mand" from my address. Thanks
 
Hi

you need VBA code which processes the BeforeSave event. If your test
criteria are nut fullfilled you set the Cancel argument to TRUE. e.g.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,Cancel As
Boolean)
IF ......(insert your test criteria or a sub function whcih will do the
evaluation)=FALSE
MsgBox "Criteria failed"
Cancel = True
Exit Sub
End If
End Sub

Frank
 
I am curious if setting up a form might be the best way to go and
perhaps a paste link back to a spreadsheet?
 
Frank Kabel,

Read your reply that it is possible to validate all fields befor
allowing to save using VBA code. That's good but as far as
understand, there is still not perfect because Excel will prompt fo
whether to run the VBA when we open the file. If the user is to clic
"No", then the VBA will not be loaded and thus the validation proces
will not be working already.

So, is there any better option apart from using VBA or can we automat
the VBA without needing the user consent?

Hope you can share your experience.

Thank you.

Best regards,
Gilber
 
Back
Top