Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change events not working properly
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula and worksheet will not working properly | Excel Worksheet Functions | |||
Macro not working properly on change in cell value | Excel Programming | |||
Worksheet Change Events | Excel Programming | |||
Worksheet Change Events | Excel Programming | |||
Worksheet Events Not Working | Excel Programming |