Take the 2-minute tour ×

Hi all i will give an Excel Document for the user with some basic information as follows

enter image description here

Now if the user leaves a cell which is required i would like to prompt him a message saying that it is a required value with in the excel.

I have referred to some articles

But i am unable to achieve what I required so can any one help. Also I would like to know is it possible to apply Regular expression validators with in the Excel. Something like Date format should be mm/dd/yyyy and SSN should be 9 digited like that..

I tried some thing like but this didn't prompt me any error or Dialog

Private Sub Worksheet_BeforeSave(Cancel As Boolean)
If Sheet1.Range("A3:B3").Value = "" Then
Application.EnableEvents = True
MsgBox "Cannot print until required cells have been completed!"
Cancel = True      
End If   
End Sub
share|improve this question
For your first question, you should probably use event procedure. For your second question, did you try to tweak Data Validation? –  JMax Nov 24 '11 at 10:29
These are two separate, unrelated questions, and therefore you should post them in two separate questions. –  Jean-François Corbett Nov 24 '11 at 12:04
add comment

2 Answers

Part 1

That code has a few issues

  1. You need a Workbook event - there is no WorkSheet_BeforeSave event
  2. You cant test for two blank cells with Sheet1.Range("A3:B3").Value = ""
  3. If the code is running then events are already enabled, so this line Application.EnableEvents = True is redundant

Something like this test for both A3 and B3 being non blank on the leftmost sheet

{code goes in the ThisWorkbook module}

  If Application.WorksheetFunction.CountA(Sheets(1).Range("a3:b3")) <> 2 Then
        MsgBox "Cannot print until required cells have been completed!"
        Cancel = True
    End If
End Sub

While you can use a Regex for part 2, plain data validation should work fine,ie

a) You can use an "allow" Date in Data Validation

b) You could use a Whole Number between 100,000,000 and 999,999,999 for a 9 digit number

share|improve this answer
add comment

Answer to Part 2 (Regular Expression thing)

You can write a user-defined function in VBA and use it in Excel. Here's an example that extracts parts of a string using RegEx, but with a bit of tweaking you can use it to do validation.

share|improve this answer
add comment

Your Answer


