Dave, in the interim I tried using a List Box instead of incell dropdown and
I'm getting somewhere. No need to worry about the worksheet change event at
this stage. The issue of #REF is still current though. Brett

"Dave Peterson" wrote:

First, I didn't try to duplicate the workbook.

But when I debug this kind of thing, I'll change the .formular1c1 and remove the
leading equal sign. Then the formulas become simple text.

Then after the code runs, I go back to excel (switch to R1C1 reference style if
required) and insert the equal sign to see how badly I messed up the formula.

Second, I would qualify the ranges.

If
Range("first.FX.payer")
belongs to the sheet being changed, I'd use:
me.Range("first.FX.payer")

If the sheet being changed is named Loans, then instead of:
Sheets("LOANS").Calculate
I'd use:
Me.Calculate

If it's another sheet in the same workbook, I'd use:
me.parent.Sheets("LOANS").Calculate

======
Most importantly, I'd add:
Option Explicit
to the top of the module.

And then declare each variable that I used in that module.


Brettjg wrote:

I have the following:

Private Sub Worksheet_Change(ByVal Target As Range)
'If Not Intersect(Target, Me.Range(Range("FX.REFI").Offset(44, 0),
Range("FX.REFI").Offset(63, 0))) Is Nothing Then
'If Not Intersect(Target, Me.Range("AG74:AG93")) Is Nothing Then
Select Case Target.Column
Case 33
Select Case Target.Row
Case 74 To 93
' Application.EnableEvents = False
MsgBox Target.Column & " " & Target.Value
Range("first.FX.payer").FormulaR1C1 =
"=IF(PERSONAL.xls!FX_STAYING(RC[1])0,PERSONAL.xls!FX_STAYING(RC[1]),next.FX.for.refi)"
cnt = 1
Do While cnt <= 7
Range("first.FX.payer").Offset(cnt,
0).FormulaR1C1 =
"=IF(PERSONAL.xls!FX_STAYING(RC[1])0,PERSONAL.xls!FX_STAYING(RC[1]),PERSONAL.xls!LEDGER_INCREMENT(R[-1]C))"
cnt = cnt + 1
Loop
MsgBox "CHANGE FIRING"
' Application.EnableEvents = True
End Select
End Select
Sheets("LOANS").Calculate
'End If
End Sub

When any cell in the correct range is changed the fisrt msgbox comes up but
absolutely nothing else happens: no formulas put in, and most importantly
(for debugging) NO SECOND MSGBOX which would tell me if the macro was firing
properly.

Some very important points to note a
calc and events are definitely on before I change the cell

You'll see that the 2 If not Intersects are commented out in the code I
posted - it doesn't matter which test I use to restrict the range, I just
happen to have left the Select Case tests in (I prefer Select to narrow down
the range in case(?) there are a few different events I want from different
ranges).

It doesn't matter whether I turns events off (as per commented out line) or
not.

This is related to an unresolved query from a fews day ago (with a more
complex change procedure) and I believe the answer to this one will lso
resolve the other.

Furthermo In this particular case I only included the change event
because the calling of the Public Finction (as shown in the formulas being
put into the cells above) doesn't update when I change one of the target
cells - the formula results are still the same as before I changed the cell.
If I can get the Public Function to update then I won't even need this above
change procedure.

However I would still need to resolve why the procedure doesn't fire so that
I can the other problem (from the other day).

Have I confused you all yet? Regards, Brett

--

Dave Peterson