Finding next available row and making data entry required

[Aprez76]

Hi all,

I'm attempting to write a macro that will "loop until", down a given column (column a) until it finds a blank cell. Once the cell is identified I would like the focus to be on that cell and remain there until an entry/input is made by a user. Perhaps even a message box when a user tries to leave the cell without entering any information to say this field is required before moving on.

Any assistance is most appreciated.

Andrew

 

[RolfJ]

You should be able to work with the following two functions. The first one sets the focus on the first empty cell in the column of interest. It requires that the focus was on a non-empty cell further up in the column:

Option Explicit
Public r As Range
Sub SetFocusOnNextEmptyCell()
    Set r = Selection.End(xlDown).Offset(1)
    r.Select
End Sub

If you now also include the following event handler in the VBA section for the worksheet of interest you should find that your user can't leave the first empty cell before he/she entered some text or number:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not r Is Nothing Then
        If r.Value = "" Then
            Set Target = r
            r.Select
        End If
    End If
End Sub

Hope this helped,
Rolf