Dependent Multi-Select Drop Down List (VBA)

[steveh1873]

Hi All,

I have made a drop down list named "Region" (In cells A3:A2500) and a dependent drop down named "Area" (In cells B3:2500), Both are made using named ranges 'Region' and 'CGM', 'HIOW', 'HLNY', 'Merseyside' or 'WY' (depending on 'Region's selection)

I have copied and pasted the following:

Private Sub Worksheet_Change(ByVal Target As Range)
'JBeaucaire 10/2/2009)
'These two macros create a multi-choice dropbox
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target <> "" Then
If Prior <> "" Then Target = Target.Text & ", " & Prior
Prior = Target
Else
Prior = ""
End If
End If
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then _
Prior = Target.Text
End Sub


It works...to a point... As I'm working through the sheet, I get the following prompt "Run-time error '94': Invalid use of Null" and am directed to the line 'Prior = Target.Text' (highlighted in red.

Can anyone help solve this or provide a new set of code based on the info i've provided?

All help is appreciated...