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??????

circular reference in VB
 

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 ,
"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??????

circular reference in VB
 

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" wrote in message
...
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??????

circular reference in VB
 

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??????

circular reference in VB
 

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??????