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

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

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

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

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

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

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