Limit Macro from Filling Past Row

[ExcelPie]

Hi, I am making a Name List where people can put their name in a cell, and click on a macro-recorded button to automatically add their name to a new row in an existing list.

However, I am trying to add VBA to set a limit to the number of rows that can be filled in this list. So, if I want the name list to be 20 rowsonly, I want the button to only fill within the 20-row range, and otherwise return an error message box for the user to go back and delete names manually.

How would I go about limiting the rows for this? Thanks in advance for your help!

 

[lrobbo314]

This code has the user entering the names in A1, and the list is being created in column D. The list in column D has a header in my test workbook. You may need to adjust some values depending on how your workbook is laid out. Here's the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
If Not Intersect(Range("A1"), Target) Is Nothing Then
    Dim LR As Long: LR = Range("D" & Rows.Count).End(xlUp).Offset(1).Row()
    If LR > 21 Then
        MsgBox "Too many names!", vbCritical, "Error"
    Else
        Range("D" & LR) = Target.Value
        Target.Value = vbNullString
    End If
End If
Target.Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[ExcelPie]

Thanks for helping me out!

That does almost everything I was looking for.
One thing though - If I have other data somewhere else in column D, the worksheet will always return an error. Is there a workaround for that?

 

[lrobbo314]

You can either choose a column other than D that does not have anything below it, or you could adjust the line of code below. So, say that your data below the list in column D begins in row 51, then you would adjust the code like this.

Dim LR As Long: LR = [B][COLOR=#0000cd]Range("D50")[/COLOR][/B].End(xlUp).Offset(1).Row()

 

[ExcelPie]

That worked just the way I need it to. Thanks again for your help.

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂