New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Create A Dynamic Dropdown Validation List

Free Excel Macros

Create A Dynamic Dropdown Validation List

Postby Sisyphus » Tue Apr 17, 2012 7:36 am

The attached module was created for an application where a limited number of repetitive entries were to be enabled in a column. Both for the purpose of simplifying entries and ensuring that repeats didn't contain typos (to enable correct filtering) a dropdown list was to be created containing all previous descriptions. The user could select from the list or add a new one. If a new description was added, it should be available in the next dropdown list. Once a selection or entry had been made the dropdown was no longer required.

This task was solved by creating a validation list on entering the cell and deleting it upon exit. Actually, the list exists only for as long as it is being used. The cell in question is the cell below the last cell in the entry column. The list contains no duplicates and it is sorted alphabetically.

The code consists of two parts, one to do the work, the other to automate it. This is the event procedure that ensures creation and deletion of the validation list as you enter or leave the cell in the next blank row.
Code: Select all
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const FirstRow As Long = 13
    Const ListColumn As Long = 2           ' = column B

    With Target
        If .Address = Cells(LastRow(ListColumn) + 1, ListColumn).Address Then
            SetValidation Target, FirstRow
        End If
    End With
   
    On Error GoTo NoPrevious
    With PrevCell
        If .Cells.Count = 1 And .Column = ListColumn Then
            SetValidation PrevCell, FirstRow, Del:=True
        End If
    End With
NoPrevious:
    Set PrevCell = Target
End Sub
Paste this procedure in the code sheet of every worksheet on which you want a validation list. In fact, some sacrifice has been made to enable you to have different validation lists in different columns on different worksheets. Note that this code will produce various error messages if you click in your worksheet while the remainder of the code isn't installed yet.

This code has two - the only two - parameters that you can (and must) adjust to your requirements:
Code: Select all
    Const FirstRow As Long = 13
    Const ListColumn As Long = 2           ' = column B
  • FirstRow is the first row containing data you want included in the validation list.
  • ListColumn is the column in which you have both, the data for the list and the cell with the dropdown.

The code that does the actual work is in the module ValMan in the attached workbook. It is good for all versions of Excel from 2003 onward. This is the easiest way of transferring it to your own project:
  • Open both workbooks, the attached one and your own project.
  • Open the VB Editor window and enable the Project Explorer window in it.
  • Expand the list of modules in the attached workbook so that you see the 'ValMan' module.
  • Drag it to your own project.

Whereas the immediate application of this code is rather specific it is easy to modify to produce validation lists for many other applications. There are several experts on this forum - including myself - who will be ready, willing and able to assist you in adapting it to your specific requirements.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 21 guests

cron