Data Validation Update Validation Selection

[Guest]

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]

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

 

[Roger Govier]

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

 

[Guest]

Thank you Roger & Debra worked like a charm.
both worked the other change I made was to make my Range Name Dynamic.

 

[HAKS]

This is a great little tool and is making my life much easier. I have just one question;


Is it possible to get the code to run on multiple worksheets? I have one list that i have sourced on a number of worksheets and would like my changes to be updated across all sheets. I tried to change the code to do so but couldn't get it to work. Any help would be great.

Cheers, Rohan

 

[HAKS]

anyone?