I am using this in workbook(1) to search text in cell D3 in a range of
workbook(2). if the text in D3 is not found in the range of wb2 then a
form opens up to - add information into wb2, save the changes in wb2,
and then close wb2. this all works fine.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Address = "$D$3" Then
If .Value < "" Then
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open("path/to/wb2")
ActiveWorkbook.Sheets("1").Activate
If Sheets("1").Range("A1:A200").Find( _
what:=.Value) Is Nothing Then
sourcewb.Close True
FORM.Show
End If
End If
End If
End With

the problem is that if D3 is found in wb2. wb2 doesn't close.
i am guessing i need an ElseIf but not sure how to code it.

any idea's?


---
Message posted from http://www.ExcelForum.com/

See one possible answer in your previous thread:

"what does (ByVal Target As Excel.Range)" mean"


In article ,
Zygoid wrote:

I am using this in workbook(1) to search text in cell D3 in a range of
workbook(2). if the text in D3 is not found in the range of wb2 then a
form opens up to - add information into wb2, save the changes in wb2,
and then close wb2. this all works fine.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Address = "$D$3" Then
If .Value < "" Then
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open("path/to/wb2")
ActiveWorkbook.Sheets("1").Activate
If Sheets("1").Range("A1:A200").Find( _
what:=.Value) Is Nothing Then
sourcewb.Close True
FORM.Show
End If
End If
End If
End With

the problem is that if D3 is found in wb2. wb2 doesn't close.
i am guessing i need an ElseIf but not sure how to code it.

any idea's?


---
Message posted from http://www.ExcelForum.com/