How do I require input into an Excel cell?

Question

I would like to require a use to input a value into a specific cell in Excel 2010. Although I know how to use the Data Validation feature, I cannot get the value to be required. That is, they can tab right out of the field and into the next one --- leaving my posting period (required field) null, which the accounting software cannot handle.

Answer 1

You can do it with some code. Right-click the worksheet tab containing the required cell and paste in the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Static LastCell As Range

    If Target.Cells.Count > 1 Then

        Exit Sub

    End If

    If LastCell Is Nothing Then

        Set LastCell = Target

    End If

    If LastCell.Address(False, False) = "A1" Then '<<< CHANGE

        If LastCell.Value = vbNullString Then

            Application.EnableEvents = False

            LastCell.Select

            MsgBox "enter a value in A1"

            Application.EnableEvents = True

        End If

        Set LastCell = Range("A1") '<<< CHANGE

    Else

        Set LastCell = Target

    End If

End Sub

Change each reference to A1 marked in the code with <<< to the cell you need validated. Once you enter that cell, the code requires that you input something. If it is empty, it remains selected.

Answer 2

You can do it with some code. Right-click the worksheet tab containing the required cell and paste in the following code:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Static LastCell As Range

    If Target.Cells.Count > 1 Then

        Exit Sub

    End If

    If LastCell Is Nothing Then

        Set LastCell = Target

    End If

    If LastCell.Address(False, False) = "A1" Then '<<< CHANGE

        If LastCell.Value = vbNullString Then

            Application.EnableEvents = False

            LastCell.Select

            MsgBox "enter a value in A1"

            Application.EnableEvents = True

        End If

        Set LastCell = Range("A1") '<<< CHANGE

    Else

        Set LastCell = Target

    End If

End Sub

Change each reference to A1 marked in the code with <<< to the cell you need validated. Once you enter that cell, the code requires that you input something. If it is empty, it remains selected.

 

 

Thank you for your solution. It works EXACTLY as I hoped. I am now trying to expand it to include requiring a second value be entered, and am struggling with it, but not giving up. I tend to be hard-headed sometimes but have found I learn better by struggling through to a solution than by simply asking for someone's help. THANK YOU VERY MUCH for your timely, accurate, and well documented solution.

Answer 3

This is exactly what I was looking for; however, how would one go about doing this for multiple cells? As far as I can determine, this will only work for a single cell.