Limit Macro from Filling Past Row

ExcelPie

New Member
Joined
Oct 26, 2019
Messages
5
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,627
Office Version
365, 2019, 2016
Platform
Windows
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.

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

New Member
Joined
Oct 26, 2019
Messages
5
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,627
Office Version
365, 2019, 2016
Platform
Windows
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.

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,627
Office Version
365, 2019, 2016
Platform
Windows
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
 

Forum statistics

Threads
1,085,546
Messages
5,384,373
Members
401,890
Latest member
Angela7

Some videos you may like

This Week's Hot Topics

Top