circular reference in VB

Hi, I have a stock program, I want the posibility that if someone put in some
cell (lets say a1) a code, in the other cell appears the description (lets
say b2). until this point everithing is easy (just adding a lookup function
in cell b2) the problem is that I want that if the user type the description
in the description cell (b1) the code appears (in cell a1), I thougt to use
the following code :

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address="$A$1" then
cells(1,2)= "vlookup(....................................
end if

if target.address="$b$1" then
cells(1,2)= "vlookup(....................................
end if

End Sub

it works just fine, the problem is that THE CODE IS EXECUTED FOR EVER, it
dosn't stop, I know exactly why it dosn't stop (because it create a circualr
reference and it's executed when a dependient cell of it self changes), but I
have no idea how to do what I want, any suggestions??????

Interrupt the events:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address="$A$1" Then
Application.EnableEvents = False
Cells(1, 2).Formula ="=VLOOKUP(...)"
Application.EnableEvents = True
ElseIf Target.Address = "$B$1" Then
Application.EnableEvents = False
Cells(1, 2).Formula ="=VLOOKUP(...)"
Application.EnableEvents = True
End IF
End Sub

or perhaps:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not Intersect(.Cells, Range("A1:B1") Is Nothing Then
Application.EnableEvents = False
If .Address(False, False) = "A1" Then
Cells(1, 2).Formula = "=VLOOKUP(...)"
Else
Cells(1, 2).Formula = "=VLOOKUP(...)"
End If
Application.EnableEvents = True
End If
End With
End Sub


In article <[email protected]>,
"filo666" <[email protected]> wrote:

> Hi, I have a stock program, I want the posibility that if someone put in some
> cell (lets say a1) a code, in the other cell appears the description (lets
> say b2). until this point everithing is easy (just adding a lookup function
> in cell b2) the problem is that I want that if the user type the description
> in the description cell (b1) the code appears (in cell a1), I thougt to use
> the following code :
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> if target.address="$A$1" then
> cells(1,2)= "vlookup(....................................
> end if
>
> if target.address="$b$1" then
> cells(1,2)= "vlookup(....................................
> end if
>
> End Sub
>
> it works just fine, the problem is that THE CODE IS EXECUTED FOR EVER, it
> dosn't stop, I know exactly why it dosn't stop (because it create a circualr
> reference and it's executed when a dependient cell of it self changes), but I
> have no idea how to do what I want, any suggestions??????

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False

If Target.Address = "$A$1" Then
Cells(1, 2) = "vlookup(...................................."
ElseIf Target.Address = "$b$1" Then
Cells(1, 2) = "vlookup(...................................."
End If

ws_exit:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"filo666" <[email protected]> wrote in message
news:[email protected]...
> Hi, I have a stock program, I want the posibility that if someone put in
some
> cell (lets say a1) a code, in the other cell appears the description (lets
> say b2). until this point everithing is easy (just adding a lookup
function
> in cell b2) the problem is that I want that if the user type the
description
> in the description cell (b1) the code appears (in cell a1), I thougt to
use
> the following code :
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> if target.address="$A$1" then
> cells(1,2)= "vlookup(....................................
> end if
>
> if target.address="$b$1" then
> cells(1,2)= "vlookup(....................................
> end if
>
> End Sub
>
> it works just fine, the problem is that THE CODE IS EXECUTED FOR EVER, it
> dosn't stop, I know exactly why it dosn't stop (because it create a
circualr
> reference and it's executed when a dependient cell of it self changes),
but I
> have no idea how to do what I want, any suggestions??????

You are catching the change event, so you are exactly correct in that you
have a circular reference. To fix this you can use

Application.enableevent = false

and

application.enableevents = true

This will turn off the firing of events so that when you make the change,
the change event will not fire. As always application level events should be
done with an error handling routine... HTH

Private Sub Worksheet_Change(ByVal Target As Range)
on error goto ErrorHandler
application.enableevents = false 'No events will fire
if target.address="$A$1" then
cells(1,2)= "vlookup(....................................
end if

if target.address="$b$1" then
cells(1,2)= "vlookup(....................................
end if

ErrorHandler:
application.enableevents = true 'Reset the events.
End Sub


"filo666" wrote:

> Hi, I have a stock program, I want the posibility that if someone put in some
> cell (lets say a1) a code, in the other cell appears the description (lets
> say b2). until this point everithing is easy (just adding a lookup function
> in cell b2) the problem is that I want that if the user type the description
> in the description cell (b1) the code appears (in cell a1), I thougt to use
> the following code :
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> if target.address="$A$1" then
> cells(1,2)= "vlookup(....................................
> end if
>
> if target.address="$b$1" then
> cells(1,2)= "vlookup(....................................
> end if
>
> End Sub
>
> it works just fine, the problem is that THE CODE IS EXECUTED FOR EVER, it
> dosn't stop, I know exactly why it dosn't stop (because it create a circualr
> reference and it's executed when a dependient cell of it self changes), but I
> have no idea how to do what I want, any suggestions??????

Thakks you all, your information was very helpfull

"Jim Thomlinson" wrote:

> You are catching the change event, so you are exactly correct in that you
> have a circular reference. To fix this you can use
>
> Application.enableevent = false
>
> and
>
> application.enableevents = true
>
> This will turn off the firing of events so that when you make the change,
> the change event will not fire. As always application level events should be
> done with an error handling routine... HTH
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> on error goto ErrorHandler
> application.enableevents = false 'No events will fire
> if target.address="$A$1" then
> cells(1,2)= "vlookup(....................................
> end if
>
> if target.address="$b$1" then
> cells(1,2)= "vlookup(....................................
> end if
>
> ErrorHandler:
> application.enableevents = true 'Reset the events.
> End Sub
>
>
> "filo666" wrote:
>
> > Hi, I have a stock program, I want the posibility that if someone put in some
> > cell (lets say a1) a code, in the other cell appears the description (lets
> > say b2). until this point everithing is easy (just adding a lookup function
> > in cell b2) the problem is that I want that if the user type the description
> > in the description cell (b1) the code appears (in cell a1), I thougt to use
> > the following code :
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > if target.address="$A$1" then
> > cells(1,2)= "vlookup(....................................
> > end if
> >
> > if target.address="$b$1" then
> > cells(1,2)= "vlookup(....................................
> > end if
> >
> > End Sub
> >
> > it works just fine, the problem is that THE CODE IS EXECUTED FOR EVER, it
> > dosn't stop, I know exactly why it dosn't stop (because it create a circualr
> > reference and it's executed when a dependient cell of it self changes), but I
> > have no idea how to do what I want, any suggestions??????