Update Validation Selection I have been to www.contextures.com and downloaded
DV0022, this is exactly what I wish to achieve when a selection in the list
is updated the data file updates to the new description. The problem is when
I add a new item to the list it updates every blank cell in the data file
colum with the new value added to the list, I don't want it to update all
blank cells when new item added to the look-up list. Please help with what I
am doing wrong.
Debra Dalgleish - 11 Sep 2007 14:27 GMT
You can add code to check for an empty string in the old value:
If strOld <> "" Then
wsData.Columns("B:B").Replace What:=strOld, _
Replacement:=strNew, LookAt:=xlPart, _
SearchOrder:=xlByRows
End If
> Update Validation Selection I have been to www.contextures.com and downloaded
> DV0022, this is exactly what I wish to achieve when a selection in the list
[quoted text clipped - 3 lines]
> blank cells when new item added to the look-up list. Please help with what I
> am doing wrong.

Signature
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html
Roger Govier - 11 Sep 2007 14:48 GMT
Hi
Debra's code was based upon a fixed range for Fruitlist, whereas I suspect
yours is Dynamic.
One way of dealing with the problem would be to test for a null string for
Old and goto the exit handler. I have marked where the inserted lines
appear.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Dim rng As Range
Dim strOld As String
Dim strNew As String
Dim wsData As Worksheet
Dim wsLists As Worksheet
Set wsLists = Sheets("Lists")
Set wsData = Sheets("Data")
Set rng = wsLists.Range("FruitList")
If Intersect(Target, rng) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
strNew = Target.Value
Application.Undo
strOld = Target.Value
' inserted lines
If strOld = "" Then
Target.Value = strNew
GoTo exitHandler
End If
' end of inserted lines
Target.Value = strNew
wsData.Columns("B:B").Replace What:=strOld, _
Replacement:=strNew, LookAt:=xlPart, _
SearchOrder:=xlByRows
Application.EnableEvents = True
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Change could not be completed"
GoTo exitHandler
End Sub

Signature
Regards
Roger Govier
> Update Validation Selection I have been to www.contextures.com and
> downloaded
[quoted text clipped - 7 lines]
> I
> am doing wrong.
PCreighton - 11 Sep 2007 15:32 GMT
Thank you Roger & Debra worked like a charm.
both worked the other change I made was to make my Range Name Dynamic.
> Hi
>
[quoted text clipped - 59 lines]
> > I
> > am doing wrong.