About Us

filo666

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
circular reference	circular reference	Excel Worksheet Functions	2	August 9th 08 10:05 PM
Circular reference	reno	Excel Discussion (Misc queries)	2	August 27th 07 10:34 PM
Circular Reference	Jim	Excel Discussion (Misc queries)	2	August 27th 07 05:22 PM
Circular reference help!	nick	Excel Discussion (Misc queries)	4	March 7th 06 08:07 PM
Circular Reference	MahaRaj®	Excel Programming	3	October 19th 04 11:32 PM

Menu

About Us