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

 

[MARK858]

Most codes you need to turn the protection off before the code and back on immediately afterwards if you are interacting with the cells or use userinterfaceonly when protecting the sheet.

Protect Excel Worksheet as UserInterFaceOnly - PK: An Excel Expert

Just assume you a worksheet wherein you have few groups for column or rows and then you protected that worksheet. Now will you be able to expand or collapse those groups? No, you cannot expand or collapse it because it will show error as worksheet is protected. If your worksheet is protected...

www.pk-anexcelexpert.com

Excel UserInterFaceOnly

Protect your worksheet and allow macros to run

excel-it.com

 

[Akuini]

HI, @kungsleden
Try this:
I assumed data entry must start at the first column .
It use "Worksheet_Change" event.
Change "Table1" to suit.
Try it without the sheet protection first, if it works then you can amend the code to include turning off-on the sheet protection.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo skip:

With ActiveSheet.ListObjects("Table1")
    If Not Intersect(Target, .Range) Is Nothing Then
       If .Range.Cells(1).End(xlDown).Row < Target.Row Then

        Application.EnableEvents = False
            Target.ClearContents
            .Range.Cells(1).End(xlDown).Offset(1).Activate
        Application.EnableEvents = True
        End If

    End If
End With
Exit Sub

skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

 

[kungsleden]

Thank you Akuini, I will try that.

I joined a few hours ago, and very pleased to get suggestions to my question very quickly. A lot to learn.