Thanks Bob. Now it works.

But let me add another complication to this.

The source list for the Data Validation is in another workbook (file); i.e.,
B1:B10 (the dataList) is actually referencing C1:C10 in another workbook. I
would like the source list to be a dynamic range, such that if I add or
change anything in the source list, it gets reflected in cell A1 and its
dropdown list, but I want the dropdown to also omit the lines that are blanks.
How can I successfully do this?

Bob Phillips wrote:
Morning Susanna,

I have just tried it again, using the cell addresses that you used, and it
is working fine as I understand it.

I have changed the code to your data, added extra error-handling, so please
replace all the code with this and try again. Note where it gets installed.

Option Explicit

Private oldValue As String
Private Const DVCell As String = "A1"
Private Const dataList As String = "B1:B10"

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Count = 1 Then
If Not Intersect(Target, Range(dataList)) Is Nothing Then
With Target
If Range(DVCell).Value = oldValue Then
Range(DVCell).Value = .Value
End If
End With
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range(dataList)) Is Nothing Then
If Not IsEmpty(Target) Then
oldValue = Target.Value
End If
End If
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Hi Bob,

[quoted text clipped - 23 lines]

Thanks


--
Message posted via http://www.officekb.com