Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / September 2007

Tip: Looking for answers? Try searching our database.

Data Validation Update Validation Selection

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
PCreighton - 11 Sep 2007 13:42 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.