data validation VBA

[ericlch16]

i have five columns A, B, C ,D ,E

the user needs to enter data in A before entering data in B or C or D or E

how to achieve this data validation in VBA? B,C,D,E I already force the user to enter today's date using data validation.

ActiveSheet.Range(Alpha_Column(m_StartDate) & irow & ":" & Alpha_Column(m_StartDate) & irow).Validation.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= xlGreaterEqual, Formula1:="=TODAY()"

can we have more data validation to B,C,D,E? so that before entering data they are forced to enter in A?

 

[JoeMo]

You could do this with a worksheet selection change event so that whenever a cell is chosen in columns B-E and the corresponding A column cell is empty, the user will be warned and the active cell will shift to A. Here's a macro to go into the worksheet module as follows:

1. Right-Click the worksheet name tab and choose 'View Code'
2. Copy the code below and paste it into the VB editor white space that appears.
3. Close the VB editor and save the workbook. If you are using Excel 2007 or Excel 2010, be sure to save the workbook with a .xlsm file extension or the code will be lost when you close the workbook.
4. When you open the workbook, you must enable macros for this to work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rw As Long
If Not Intersect(Target, Columns("B:E")) Is Nothing Then
    If Target.Count = 1 Then
        Rw = Target.Cells(1, 1).Row
        If IsEmpty(Cells(Rw, 1)) Then
            MsgBox "Fill Cell A" & Rw & " first!"
            Cells(Rw, 1).Select
        End If
    Else
        For Each c In Target
            If IsEmpty(Cells(c.Row, 1)) Then
                Rw = c.Row
                MsgBox "A column cells must be filled first!"
                Cells(Rw, 1).Select
                Exit Sub
            End If
        Next c
    End If
End If
End Sub