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.

To make an input in a cell mandatory without leaving it blank, you can use the Data Validation feature in Microsoft Excel. Here are the steps:

1. Select the cell or range of cells where you want to apply the mandatory input.
2. Go to the Data tab in the ribbon and click on the Data Validation button.
3. In the Data Validation dialog box, choose "Custom" from the Allow drop-down list.
4. In the Formula box, enter a formula that checks if the cell is not blank. For example, you can use the formula
   Formula:

   =LEN(A1)0 

   if you want to make sure that the input in cell A1 is not empty.
5. Optionally, you can add a custom error message to inform the user about the mandatory input. Click on the Error Alert tab and enter a title and message for the error.
6. Click OK to close the Data Validation dialog box.

Now, if the user tries to leave the cell blank or enter an invalid input, Excel will display the error message and prevent them from saving the worksheet until the input is corrected.

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

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

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