execute code in worksheet_change

  • Thread starter Thread starter gatarossi
  • Start date Start date
G

gatarossi

Dear all,

I need to execute some code in worksheet_change then I did it:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then
Call return_accounting_code
End If

If Not Intersect(Target, Range("A7:A65536")) Is Nothing Then
Call return_accounting_code_description
End If


End Sub

But it doesn't work, I think here we have like a "circular reference",
because first I need to return only the accounting code (When I change
the cell B3 (that contains the profit center) excel returns from
access all accounting code for the profit center selected.

And after it, - of course, the column A - accounting code will change
- I need to run another VBA code to excel returns from access the
description of the new accountings codes.

How can I solve this circular reference?

I need first put the new accounting_code (until here, it's ok!), but
when I put the other procedure, excel doesn't understand it...

One important thing - I need to execute it in two parts!!! I known if
I put like above, it will run, but sometimes, I need to change only
the accounting code and not the profit center too!!!

If Target.Address = "$B$3" Then
Call retorna_conta_contabil
Call retorna_descricao_conta_contabil
End If

Thanks a lot !!!
 
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Goto ws_exit:
Application.EnableEvents = False

If Target.Address = "$B$3" Then
Call return_accounting_code
End If

If Not Intersect(Target, Range("A7:A65536")) Is Nothing Then
Call return_accounting_code_description
End If

ws_exit:
Application.EnableEvents = True
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Maybe your subroutines return those values back into the same worksheet. And
when they write the values to the worksheet, they cause the worksheet_Change
event to fire again (which may cause the routines to be executed again -- and
that fires the worksheet_change event) and over and over and over--until excel
gets tired.

It may be as simple as:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then
application.enableevents = false
Call return_accounting_code
application.enableevents = True
Elseif Not Intersect(Target, Range("A7:A65536")) Is Nothing Then
application.enableevents = false
Call return_accounting_code_description
application.enableevents = true
End If

End Sub
(I changed your two if/endif's statements.)

===========
Depending on what these procedures do, it may work. If the code in those other
procedures turn events back on, then it may not.

You may want to inspect the code in both of those procedures and add the
..enableevents lines whereever necessary (when you update a cell).
 
Back
Top