Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?????? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?????? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?????? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?????? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?????? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
circular reference | Excel Worksheet Functions | |||
Circular reference | Excel Discussion (Misc queries) | |||
Circular Reference | Excel Discussion (Misc queries) | |||
Circular reference help! | Excel Discussion (Misc queries) | |||
Circular Reference | Excel Programming |