Worksheet Change events not working properly

B

Brettjg

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 are:
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.

Furthermore: 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
 
J

joel

I don't think worksheet change events can write to differnet worksheets. I
can read other sheets but not write.
 
B

Brettjg

Hi Joel, everything here is within the sheet "LOANS", not even reading
anything outside "LOANS".

joel said:
I don't think worksheet change events can write to differnet worksheets. I
can read other sheets but not write.

Brettjg said:
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 are:
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.

Furthermore: 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
Click to expand...
Click to expand...
 
J

joel

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 said:
Hi Joel, everything here is within the sheet "LOANS", not even reading
anything outside "LOANS".

joel said:
I don't think worksheet change events can write to differnet worksheets. I
can read other sheets but not write.

Brettjg said:
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 are:
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.

Furthermore: 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
Click to expand...
Click to expand...
Click to expand...
 
B

Brettjg

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 said:
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 said:
Hi Joel, everything here is within the sheet "LOANS", not even reading
anything outside "LOANS".

joel said:
I don't think worksheet change events can write to differnet worksheets. I
can read other sheets but not write.

:

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 are:
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.

Furthermore: 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
Click to expand...
Click to expand...
Click to expand...
 
B

Brettjg

THIS POST IS NO LONGER REQUIRED

Brettjg said:
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 said:
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 said:
Hi Joel, everything here is within the sheet "LOANS", not even reading
anything outside "LOANS".

:

I don't think worksheet change events can write to differnet worksheets. I
can read other sheets but not write.

:

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 are:
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.

Furthermore: 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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Thanks <vbg>
THIS POST IS NO LONGER REQUIRED

Brettjg said:
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 said:
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.

:

Hi Joel, everything here is within the sheet "LOANS", not even reading
anything outside "LOANS".

:

I don't think worksheet change events can write to differnet worksheets. I
can read other sheets but not write.

:

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 are:
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.

Furthermore: 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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Worksheet Chnage....still need help please 4
Select Case code error 8
Change_Event not responding to change made by formula. 11
Public function returns error intermitently 6
Find the correct sheet then find a value on that sheet 8
Make VBA ComboBox_Change work in 3 sheets 1
how to use Solver in Excel VBA 1
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4

Top