Posted on 31 January 2008
As the title indicates, the post is all about creating a drop down list in Excel from ranges on different sheets. How is this tricky? Well, first excel allows you to use a single list as a dropdown for cell validation. Second, UNION is not permitted between ranges. Third, a collection can not be used for data validation. Fourth, no custom function/method from VBA can be invoked in the cell validation formula. So this complicates the matter a bit.
I recently had to achieve this, so as to self-audit the entries. This particularly comes in handy when one is trying to link entries across multiple sheets to a single ID.
Say we have two ranges called
Two which occur in sheets
Sheet3. We want to create a drop down in
Sheet1 with all values from the two
ranges in cells
A1:A10. You would need to create a formula in a surrogate cell (say
=createdropdown(A1:A10,One,Two). The following code is used
to achieve the feat.
Public Function mergeRange(ByVal range1 As Range, ByVal range2 As Range) As Collection Dim resultSet As New Collection For Each iCell In range1 If Not IsEmpty(iCell.Value) Then resultSet.Add (iCell.Value) End If Next For Each iCell In range2 If Not IsEmpty(iCell.Value) Then resultSet.Add (iCell.Value) End If Next Set mergeRange = resultSet End Function Public Function createDropDown(ByVal cells As Range, ByVal range1 As Range, ByVal range2 As Range) As Boolean Dim intIndex As Long Dim strContent As String Dim items As Collection Set items = mergeRange(range1, range2) 'build the string to pass to the dropdown For Each Item In items strContent = strContent & "," & Item Next 'build dropdown With cells.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strContent .IgnoreBlank = True .InCellDropdown = True .InputTitle = "Select a value" .ErrorTitle = "You entered a wrong value!" .InputMessage = "Please select an item from the list!" .ErrorMessage = "Valid values are from the list only!" .ShowInput = True .ShowError = True End With createDropDown = True End Function
The code is pretty self-explanatory. We use a custom function mergeRange to build a collection of all non-NULL values from the two ranges, and then apply the data validation to cells required using VBA.
It took time to reach the solution with my minimal knowledge of Excel DOM - but it was worth a try.
Hope this helps.