' mDropDown Module
' This version includes the sort for Excel 2003
Option Explicit
' =========================================================================== '
Sub CreateDropDown2()
Dim awf As WorksheetFunction: Set awf = WorksheetFunction
Dim lLR As Long ' Last Row on Issues sheet based on column A
' Change the cell address in the next line to move the Dependent Drop Down List
Dim sDDDL As String: sDDDL = Range("E3").Address
' Change the cell address in the next line to move the DV
Dim sWIP As String: sWIP = Range("AA1").Address
Dim sDVStart As String ' DV List Start Cell (absolute)
Dim sDVCol As String ' DV List Column
Dim sDVRange As String ' DV Dynamic Range address
Dim sDVString As String ' DV List as a concatenated string
Dim sDVSortStart As String ' DV List Sort Start Cell (absolute)
Dim sDVSortRange As String ' DV Sort Range address
With Sheets("Clients2")
' clear the Dependent Drop Down List box
Application.EnableEvents = False
.Range(sDDDL).Value = ""
Application.EnableEvents = True
End With
' set up ranges based on sWIP (defined and set above)
With Sheets("Issues")
' first, establish the last row
lLR = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range(sWIP)
' set up Data Validation Range
' If sWIP is "H1", SDVStart will be "$I$2"
sDVStart = .Offset(1, 1).Address
sDVSortStart = .Offset(0, 1).Address
' sDVCol will be "$I:$I"
sDVCol = Split(sDVStart, "$")(1)
sDVCol = "$" & sDVCol & ":$" & sDVCol
' sDVRange will be "=Issues!$I$2:INDEX(Issues!$I:$I,COUNTA(Issues!$I:$I))"
sDVRange = _
"=Issues!" & _
sDVStart & _
":INDEX(Issues!" & _
sDVCol & _
",COUNTA(Issues!" & _
sDVCol & _
"))"
sDVSortRange = _
"=Issues!" & _
sDVSortStart & _
":INDEX(Issues!" & _
sDVCol & _
",COUNTA(Issues!" & _
sDVCol & _
"))"
' set up Advanced Filter
.Value = "ID" ' $H$1 = "ID"
.Offset(0, 1).Value = "NUM" ' $I$1 = "NUM"
.Offset(1, 0).Value = _
Sheets("Clients2").Range("B1") ' $H$2 = ID to be selected
End With
' Extract list using Advanced Filter
.Range("A1:D" & lLR).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range(sWIP).Resize(2, 1), _
CopyToRange:=.Range(sWIP).Offset(0, 1), _
Unique:=True
End With
' Add the Data Validation Named Range
With ActiveWorkbook
On Error Resume Next
.Names("DV_NUM").Delete
On Error GoTo 0
.Names.Add _
Name:="DV_NUM", _
RefersTo:=sDVRange
' sDVRange will be something like:
' "=Issues!$I$2:INDEX(Issues!$I:$I,COUNTA(Issues!$I:$I))"
' and will refer to, for example: $I$2:$I$6
End With
' sort the Data List
With ActiveWorkbook.Worksheets("Issues")
' Excel 2003 Sort
.Range(sDVSortRange).Sort _
Key1:=.Range(sDVSortStart), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
' Concatenate the entries into a string
sDVString = ConcatRange(Range("DV_NUM"), ",")
' Add the Data Validation to the Dependent Drop Down List
With Sheets("Clients2")
With .Range(sDDDL).Validation
.Delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=sDVString ' using the DV string
' Formula1:="=DV_NUM" ' using the DV Named Range
.IgnoreBlank = False
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With
' expose the first entry in the Dependent Drop Down List
With Sheets("Clients2")
' set the Dependent Drop Down List box
Application.EnableEvents = False
.Range(sDDDL).Value = Range("DV_NUM")(1, 1)
Application.EnableEvents = True
End With
' List Variables in the Immediate Window for Testing
DebugData lLR, sDDDL, sWIP, sDVStart, sDVCol, sDVRange, sDVString, sDVSortStart, sDVSortRange
' Clear WIP area
With Sheets("Issues")
With .Range(sWIP)
'.Resize(1, 2).EntireColumn.Clear ' to remove the list
.Resize(1, 1).EntireColumn.Clear ' to retain the list
End With
End With
' Clear the Named Range
With ActiveWorkbook
On Error Resume Next
' comment out the next row if you want to leave in place
'.Names("DV_NUM").Delete
On Error GoTo 0
End With
End Sub
' =========================================================================== '
Function ConcatRange(Rng As Range, Optional Separator As String)
Dim awf As WorksheetFunction: Set awf = WorksheetFunction
Dim r As Range
For Each r In Rng
ConcatRange = ConcatRange & awf.Text(r.Value, "000") & Separator
Next
ConcatRange = Left(ConcatRange, Len(ConcatRange) - Len(Separator))
End Function
' =========================================================================== '
Sub Test1()
MsgBox ConcatRange(Range("DV_NUM"), ",")
End Sub
' =========================================================================== '
Sub Test2()
MsgBox ConcatRange(Range("DV_NUM"))
End Sub
' =========================================================================== '
Sub Test3()
MsgBox ConcatRange(Sheets("Issues").Range("D2:D6"), ",")
End Sub
' =========================================================================== '
Sub DebugData(lLR, sDDDL, sWIP, sDVStart, sDVCol, sDVRange, sDVString, sDVSortStart, sDVSortRange)
If Sheets("Issues").Range("E1") <> "Debug" Then Exit Sub
Debug.Print "lLR", lLR
Debug.Print "sDDDL", sDDDL
Debug.Print "sWIP", sWIP
Debug.Print "sDVStart", sDVStart
Debug.Print "sDVCol", sDVCol
Debug.Print "sDVRange", sDVRange
Debug.Print "sDVString", sDVString
Debug.Print "sDVSortStart", sDVSortStart
Debug.Print "sDVSortRange", sDVSortRange
End Sub
' =========================================================================== '
' End of Code
lLR 2546
sDDDL $E$3
sWIP $AA$1
sDVStart $AB$2
sDVCol $AB:$AB
sDVRange =Issues!$AB$2:INDEX(Issues!$AB:$AB,COUNTA(Issues!$AB:$AB))
sDVString 215,237,296,360,413
sDVSortStart $AB$1
sDVSortRange =Issues!$AB$1:INDEX(Issues!$AB:$AB,COUNTA(Issues!$AB:$AB))
lLR 2546
sDDDL $E$3
sWIP $AA$1
sDVStart $AB$2
sDVCol $AB:$AB
sDVRange =Issues!$AB$2:INDEX(Issues!$AB:$AB,COUNTA(Issues!$AB:$AB))
sDVString 015
sDVSortStart $AB$1
sDVSortRange =Issues!$AB$1:INDEX(Issues!$AB:$AB,COUNTA(Issues!$AB:$AB))
Calculating
Calculating
Bookmarks