Force data entry in next empty row

[kungsleden]

Hi,
I just registered. Quite new to VBA but have used Excel for quite a few years.
I am working on a log sheet for a piece of equipment. When a user uses the log sheet, I would like them to be able to enter data only in the first blank row. At the moment, they can enter data in any of the empty rows.

Regards,

Christophe

 

[Michael M]

Maybe this in the sheet module, assumes you need column "A"

Private Sub Worksheet_selectionChange(ByVal Target As Range)
Dim A As Range, Lr As Long
Set A = Range("A:A")
Lr = Cells(Rows.Count, "A").End(xlUp).Row
If Not Intersect(Target, A) Is Nothing Then
Cells(Lr + 1, 1).Select
End If
End Sub

 

[kungsleden]

Thanks Michael.
It sort of works. But not.

I already have a Worksheet_selectionChange Sub in that sheet module. It is part of a code that locks cells when the user enters data in them. The range (a table) is A12:J132.
I added your code in the selectionChange sub. When the event triggers the cell selected is the first blank one after the table/range, i.e. A133.
And what I really want is that, if rows 12-15 are already filled, the user should only be able to enter data in cells in row 16.
Makes sense?

 

[MARK858]

Does the Table have Formulas in all the cells in column A with the "blank" cells returning "" or a totals row at the bottom of the Table?
Are the cells with data in column A Constants or Formulas?

 

[kungsleden]

No formula in column A, it is a drop-down list of users' initials. Cells in the A column are formatted as Input cells and there is also conditional formatting. Are they then considered "not nothing"?
This is the code:

Dim mRg As Range
Dim mStr As String
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("A12:J132"), Target) Is Nothing Then
    Set mRg = Target.Item(1)
    mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A12:J132"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="PW"
    If xRg.Value <> mStr Then xRg.Locked = True
    Target.Worksheet.Protect Password:="PW"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Dim A As Range, Lr As Long
If Not Intersect(Range("A12:J132"), Target) Is Nothing Then
    Set mRg = Target.Item(1)
     mStr = mRg.Value
End If
'Set A = Range("A12:A132")
'Lr = Cells(Rows.Count, "A").End(xlUp).Row
'If Not Intersect(Target, A) Is Nothing Then
'Cells(Lr + 1, 1).Select
'End If
End Sub

 

[MARK858]

If it is an actual table (your code doesn't look like it is) then you can try the code below to replace the green part.
It selects the next row in the table as Michaels code did but to force the user to use it then you would need to lock every other cell on the sheet and protect the sheet.
You also haven't answered if there is a totals row at the bottom of the table.
The code assumes that it is the only table on the sheet, if not then you will to amend it to the tables name and also assumes that there are no blank cells in the rows between the data.

    With ActiveSheet.ListObjects(1).DataBodyRange
        .Rows(1).Offset(.Columns(1).SpecialCells(2).Count).Select
    End With

 

[kungsleden]

Thank you Mark,

I will try your new code.
It is a table. So one should reference table by a name rather than a range. I went through a lot of vba videos quickly without writing down much. There is no total rows, this is not a calculation sheet.
Would using a userform be simpler to achieve this?

 

[MARK858]

So one should reference table by a name rather than a range.

The way you code for a table normally is different to how you code for a range. Too much to go into now but have a look at the links below when you have a chance.

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

jkp-ads.com

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Would using a userform be simpler to achieve this?

Not really. It would make it a bit more difficult for the user to avoid using the wrong cell but at the end of the day but to force them to use the correct cells then you would still have to lock every other cell.

 

[kungsleden]

Will check your link.

How would go about achieving my goal?
I am in a business where having an empty row between filled rows is not acceptable, however unlikely this is expected to happen. I thought of adding rows to the table as they are getting populated, but thought this was even more complicated.

 

[kungsleden]

Your code does not work on protected sheets apparently.