| 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change events not working properly
I don't think worksheet change events can write to differnet worksheets. I
can read other sheets but not write. "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change events not working properly
Hi Joel, everything here is within the sheet "LOANS", not even reading
anything outside "LOANS". "joel" wrote: I don't think worksheet change events can write to differnet worksheets. I can read other sheets but not write. "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change events not working properly
You either need more msgboxes or step through the code. Make sure your
changes are in the corrrect row and column. The code looks good, but it is very dependant on the cell where you make the change and I can't isolate the problem without you data and nowing the cell that you are changing. "Brettjg" wrote: Hi Joel, everything here is within the sheet "LOANS", not even reading anything outside "LOANS". "joel" wrote: I don't think worksheet change events can write to differnet worksheets. I can read other sheets but not write. "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change events not working properly
Hi Joel
I have a feeling that I need an application.volatile in the function (I found a reference to that from Fredrik in 2005 - still on this site since then). I've put that in some other functions that I was griping about an will test it shortly in this one. Regards, Brett "joel" wrote: You either need more msgboxes or step through the code. Make sure your changes are in the corrrect row and column. The code looks good, but it is very dependant on the cell where you make the change and I can't isolate the problem without you data and nowing the cell that you are changing. "Brettjg" wrote: Hi Joel, everything here is within the sheet "LOANS", not even reading anything outside "LOANS". "joel" wrote: I don't think worksheet change events can write to differnet worksheets. I can read other sheets but not write. "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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change events not working properly
THIS POST IS NO LONGER REQUIRED
"Brettjg" wrote: Hi Joel I have a feeling that I need an application.volatile in the function (I found a reference to that from Fredrik in 2005 - still on this site since then). I've put that in some other functions that I was griping about an will test it shortly in this one. Regards, Brett "joel" wrote: You either need more msgboxes or step through the code. Make sure your changes are in the corrrect row and column. The code looks good, but it is very dependant on the cell where you make the change and I can't isolate the problem without you data and nowing the cell that you are changing. "Brettjg" wrote: Hi Joel, everything here is within the sheet "LOANS", not even reading anything outside "LOANS". "joel" wrote: I don't think worksheet change events can write to differnet worksheets. I can read other sheets but not write. "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change events not working properly
Thanks <vbg
Brettjg wrote: THIS POST IS NO LONGER REQUIRED "Brettjg" wrote: Hi Joel I have a feeling that I need an application.volatile in the function (I found a reference to that from Fredrik in 2005 - still on this site since then). I've put that in some other functions that I was griping about an will test it shortly in this one. Regards, Brett "joel" wrote: You either need more msgboxes or step through the code. Make sure your changes are in the corrrect row and column. The code looks good, but it is very dependant on the cell where you make the change and I can't isolate the problem without you data and nowing the cell that you are changing. "Brettjg" wrote: Hi Joel, everything here is within the sheet "LOANS", not even reading anything outside "LOANS". "joel" wrote: I don't think worksheet change events can write to differnet worksheets. I can read other sheets but not write. "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 |
| Reply |
| 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 | |||
Linear Mode
