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
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.