• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

How to make data validation list drop-down appear in first column of new row in VBA?

How do you set up the table and/or data validation list to be available on the first blank row of a table in VBA?

Thanks
0
mlagrange
Asked:
mlagrange
  • 2
1 Solution
 
Patrick MatthewsCommented:
An approach that worked for me:

1) Create your table

2) Resize your table so that it includes an empty row at the bottom

3) Add this code to the sheet module for the worksheet with the table, to force the table to resize with the extra blank row

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim lo As ListObject
    Dim loRng As Range
    Dim loLastRow As Range
    
    Set lo = Me.ListObjects(1)
    Set loRng = lo.Range
    Set loLastRow = loRng.Rows(loRng.Rows.Count)
    
    If Not Intersect(loLastRow, Target) Is Nothing Then
        Application.EnableEvents = False
        lo.Resize loRng.Cells(1).Resize(loRng.Rows.Count + 1, loRng.Columns.Count)
        Application.EnableEvents = True
    End If
    
End Sub

Open in new window

0
 
mlagrangeAuthor Commented:
Thanks, Patrick!
0
 
Patrick MatthewsCommented:
You're most welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now