So if I open your workbook and do about 80% of the data entry, I can't save my
work if I'm interrupted--Maybe it's time to go home. Maybe I have something
important to do. Maybe I just like saving after I spend a lot of time entering
data. Maybe I have to do some research--depending on another person and they're
not available.

So you want to make it so that I can't save my work. You want me to discard
those changes and start over later?

If the user doesn't disable macros and doesn't disable events, then you could
use a macro. This goes behind the ThisWorkBook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myRng As Range

With Me.Worksheets("Sheet999")
Set myRng = .Range("a1:a12,c13,d44:d55")
End With

If Application.Count(myRng) < myRng.Cells.Count Then
MsgBox "you can't save this!"
Cancel = True
End If
End Sub

I really don't like this kind of thing (from a user standpoint).

If I were doing it, I would create a new worksheet. Name it something like
"Errors and Warnings"

And put a bunch of formulas in column A that do your validation checks. And
then put a nice short description in column B (to whatever).

In a2:
=if(sheet999!a1<"","ok","Error")
in b2:
You have to complete the entry for xxxxx in A1 of Sheet999

Then apply data|filter|autofilter to column A. Hide the values that return Ok.




Sandy wrote:

Hi Dave,
Have a problem on this. I have many columns of different names and don't
have an adjacent cell to put your formula to pop up. Also, I want to prevent
them from saving the worksheet unless all the required cells are filled up
with values like we have in Internet online form. Is it related to a macro?

"Dave Peterson" wrote:

I use an adjacent cell with a formula like:

=if(a1<"","","Please enter something in A1")

And I format it as big bold red letters.

Sandy wrote:

How to make an input in a cell mandatory without leaving it blank like we
have for any online form where name field is mandatory or else form wouldn't
be saved.

--

Dave Peterson


--

Dave Peterson

Hi Dave,

I know its been a few years but i have a real use for the VBA code you wrote below and find that it is really useful on my data entry of only 5 cells.

I have one small problem that it only allows for numerics and not alphanumerics. How can i change this to allow letters and/or numbers.

Thanks

Matthew Jennings