VBA Express : Excel - Force the user to enter date in a cell

Excel

Force the user to enter date in a cell

Ease of Use

Easy

Version tested with

Submitted by:

mgh_mgharish

Description:

This code will force the user to enter the date in two cells. It will not allow him to skip or keep those cells blank.

Discussion:

Suppose you have a formula which requires that there should be date in two cells. If there is no date in those cells, that formula may cause error. So, to avoid this situation, this code will compel the user to enter dates before continuing. He, in no way, can escape from entering the dates.

Code:

instructions for use

			Private Sub Worksheet_Activate() 
    If (Not IsDate(Cells(1, 1).Value)) Then 
        Cells(1, 1).Value = InputBox("Enter a date in A1 cell", "No Date") 
        Worksheet_Activate 
    ElseIf (Not IsDate(Cells(1, 2).Value)) Then 
        Cells(1, 2).Value = InputBox("Enter a date in B1 cell", "No Date") 
        Worksheet_Activate 
    End If 
End Sub 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    Worksheet_Activate 
End Sub 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    Worksheet_Activate 
End Sub

How to use:

Open Excel workbook where the code is to be applied.
Press Alt+F11 or Goto Tools --> Macro --> Visual Basic Editor.
In the Project Explorer Window, double click the Sheet where you want to place the code.
Paste the code in the code pane.
Change the cells if required.
Close VB Editor by clicking X or pressing Alt+F11 again.

Test the code:

Place any value (number, date, text) in A1 or B1.
Whatever you do, the code asks you to enter the date in those cells before continuing.
Try deleting, editing, or pasting the values to see the resulting message.

Sample File:

force.zip 4.53KB

Approved by mdmackillop

This entry has been viewed 196 times.