Worksheet Chnage....still need help please

[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

 

[Dave Peterson]

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.

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

 

[Brettjg]

Hi Dave

I just posted this reply to you but something very odd happened, so I'm
going to post it again (and copy to clipboard this time)

The Public functions seems to have been resolved by including
Application.Volatile, but there are two other areas of concern. The second is
the more important and I'll come to that.

In this workbook that I'm fooling around with there are various little
groups of cells that get data from another book via vlookup. Any cell that
has an actual value to return is fine but any that returnd blank (nothing in
the other workbook) got to #REF every now and then. Nothing has changed in
either group of cells *this book or the other) and if I update links
everything is back to normal again until maybe another macro runs. This never
used to happen, and I know I could run update links in the macro that I think
MAY be causing it (if it's a macro at all) but that seems inelegant and
unecessary. Do you have any thoughts on this please?

The main problem that I'm having is with the following change procedure:
(I'll add some notes at the bottm)


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Integer, cl As Integer, rw_L As Integer, rw_Sec As Integer,
spt_abs As Variant, loan_total As Long, answer As Variant
If Target.Count > 1 Then Exit Sub
If Not Sheets("LOANS").Range("QUO.RUNNING") = "RUNNING" Then: Exit Sub
rw = Target.Row
cl = Target.Column

Select Case rw 'LOAN PRODUCT CHANGE
Case 14, 53, 92, 131, 170
Select Case cl
Case 4, 7, 10, 13, 16: GoTo CHANGE_LOAN_PRODUCT ':
Application.EnableEvents = False
Case Else: Exit Sub
End Select
End Select

Select Case rw 'LOAN CHANGE
Case 39, 78, 117, 156, 195
Select Case cl
Case 4, 7, 10, 13, 16: Application.EnableEvents = False:
GoTo LOAN_CHANGE
Case Else: Exit Sub
End Select
End Select

Select Case rw 'R or U CHANGE
Case 13, 52, 91, 130, 169
Select Case cl
Case 3, 6, 9, 12, 15
If (Cells(rw + 25, 17).Value + Cells(rw - 4, 7)) /
Cells(rw - 8, 7) > 0.8 Then: Application.EnableEvents = False: GoTo RU_CHANGE
Case Else: Exit Sub
End Select
End Select
Exit Sub

CHANGE_LOAN_PRODUCT:
'countRX = Range("RX.count").Value
'countYX = Range("YX.count").Value

rw = Target.Row
cl = Target.Column
MsgBox "FROM CHANGE_LOAN 1"
MsgBox "A = " & (rw + 25) / 39 & " Target.Value = " & Target.Value & "
SPLIT = " & (cl - 1) / 3
Target.Value = 100
Target.Offset(-18, 0).FormulaR1C1 =
"=IF(R[18]C)>10000,PERSONAL.xls!REFI_PROD_CODE(R[18]C),)"
MsgBox "FROM CHANGE_LOAN 2"
Target.Value = Target.Offset(-18, 0).Value
'spt_abs = Cells(rw + 1, cl + 1).Value
'MsgBox "A = " & (rw + 25) / 39 & " Target.Value = " & Target.Value & "
SPLIT = " & (cl - 1) / 3
'Application.Run "PERSONAL.xls!RESET_REFINANCE", (rw + 25) / 39,
Target.Value, (cl - 1) / 3
MsgBox "RETURN from RESET_REFINANCE"

GoTo EXIT_SUB 'NOTHING ELSE QUALIFIES

LOAN_CHANGE:
Application.ScreenUpdating = False
Cells(rw, cl + 1).Value = Cells(rw, cl).Value
rw_L = rw: rw_Sec = rw - 34
Cells(rw_L - 1, 17).Value = Cells(rw_L, 5).Value + Cells(rw_L, 8).Value +
Cells(rw_L, 11).Value + Cells(rw_L, 14).Value + Cells(rw_L, 17).Value
GoTo LVR_CHECK

RU_CHANGE:
Application.ScreenUpdating = False
rw_L = rw + 26: rw_Sec = rw - 8: cl = cl + 1

LVR_CHECK:
loan_total = Cells(rw_L, 5).Value + Cells(rw_L, 8).Value + Cells(rw_L,
11).Value + Cells(rw_L, 14).Value + Cells(rw_L, 17).Value
If (loan_total + Cells(rw_Sec + 4, 7)) / Cells(rw_Sec, 7) <= 0.8 Then:
Cells(rw_L - 3, 19).ClearContents: Cells(rw_L - 4, 19).ClearContents: GoTo
EXIT_SUB

GET_LMI:
Application.Run "PERSONAL.xls!WAV_DING"
answer = MsgBox("ARE YOU READY TO CALCULATE THE LMI PREMIUM YET?",
vbOKCancel + vbDefaultButton2 + vbQuestion, "CALCULATE LMI PREMIUM")
If answer = 2 Then: GoTo EXIT_SUB

Application.Calculation = xlCalculationManual
Cells(rw_L - 4, 19).ClearContents: Cells(rw_L - 3, 19).ClearContents
Application.Run "PERSONAL.xls!GET_LMI_APP", rw_L, cl

EXIT_SUB:
If Application.Calculation = xlCalculationManual Then:
Application.Calculation = xlCalculationAutomatic
If Application.EnableEvents = False Then: Application.EnableEvents = True
If Application.ScreenUpdating = False Then: Application.ScreenUpdating = True
End Sub


There are 3 main areas of execution"
one for LOAN_CHANGE and it works perfectly
one for RU_CHANGE and it works perfectly
one for CHANGE_LOAN_PRODUCT and it is a very difficult child indeed.

I use Select Case to narrow the field because the code is brief and very
specific.

I know that CHANGE_LOAN_PRODUCT startst becasue the first two Msgboxes come
up, but from the Target.value = 100 nothing happens. The most important line
to execute is 'Application.Run "PERSONAL.xls!RESET_REFINANCE" but I have
commented that out because it won't work from here (works perfectly from 5
click events however).

There is a selection change sub but the Select Case in there eleiminates any
confusion with these cells (commenting it and the click subs out makes no
difference). I've tried various other simple instructions where I have
Target.Value = 100 but nothing at all will go. I only put that particular
line in for debugging (100 is one of the values that can be returned by the
Public Function). After that I'm struggling. Regards, Brett

 

[Brettjg]

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

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.

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

 

[Dave Peterson]

First, using application.volatile isn't really a fix. Read those other posts
about how calculation works.

Second, I'd try to get the formula working manually before I tried to make it
work in code. Try that with one of the workbooks that caused the trouble.

I don't think I can offer any more suggestions. Setting up test workbooks
wouldn't be worth it. If there's a mistyped name, I'd never see it.

Hi Dave

I just posted this reply to you but something very odd happened, so I'm
going to post it again (and copy to clipboard this time)

The Public functions seems to have been resolved by including
Application.Volatile, but there are two other areas of concern. The second is
the more important and I'll come to that.

In this workbook that I'm fooling around with there are various little
groups of cells that get data from another book via vlookup. Any cell that
has an actual value to return is fine but any that returnd blank (nothing in
the other workbook) got to #REF every now and then. Nothing has changed in
either group of cells *this book or the other) and if I update links
everything is back to normal again until maybe another macro runs. This never
used to happen, and I know I could run update links in the macro that I think
MAY be causing it (if it's a macro at all) but that seems inelegant and
unecessary. Do you have any thoughts on this please?

The main problem that I'm having is with the following change procedure:
(I'll add some notes at the bottm)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Integer, cl As Integer, rw_L As Integer, rw_Sec As Integer,
spt_abs As Variant, loan_total As Long, answer As Variant
If Target.Count > 1 Then Exit Sub
If Not Sheets("LOANS").Range("QUO.RUNNING") = "RUNNING" Then: Exit Sub
rw = Target.Row
cl = Target.Column

Select Case rw 'LOAN PRODUCT CHANGE
Case 14, 53, 92, 131, 170
Select Case cl
Case 4, 7, 10, 13, 16: GoTo CHANGE_LOAN_PRODUCT ':
Application.EnableEvents = False
Case Else: Exit Sub
End Select
End Select

Select Case rw 'LOAN CHANGE
Case 39, 78, 117, 156, 195
Select Case cl
Case 4, 7, 10, 13, 16: Application.EnableEvents = False:
GoTo LOAN_CHANGE
Case Else: Exit Sub
End Select
End Select

Select Case rw 'R or U CHANGE
Case 13, 52, 91, 130, 169
Select Case cl
Case 3, 6, 9, 12, 15
If (Cells(rw + 25, 17).Value + Cells(rw - 4, 7)) /
Cells(rw - 8, 7) > 0.8 Then: Application.EnableEvents = False: GoTo RU_CHANGE
Case Else: Exit Sub
End Select
End Select
Exit Sub

CHANGE_LOAN_PRODUCT:
'countRX = Range("RX.count").Value
'countYX = Range("YX.count").Value

rw = Target.Row
cl = Target.Column
MsgBox "FROM CHANGE_LOAN 1"
MsgBox "A = " & (rw + 25) / 39 & " Target.Value = " & Target.Value & "
SPLIT = " & (cl - 1) / 3
Target.Value = 100
Target.Offset(-18, 0).FormulaR1C1 =
"=IF(R[18]C)>10000,PERSONAL.xls!REFI_PROD_CODE(R[18]C),)"
MsgBox "FROM CHANGE_LOAN 2"
Target.Value = Target.Offset(-18, 0).Value
'spt_abs = Cells(rw + 1, cl + 1).Value
'MsgBox "A = " & (rw + 25) / 39 & " Target.Value = " & Target.Value & "
SPLIT = " & (cl - 1) / 3
'Application.Run "PERSONAL.xls!RESET_REFINANCE", (rw + 25) / 39,
Target.Value, (cl - 1) / 3
MsgBox "RETURN from RESET_REFINANCE"

GoTo EXIT_SUB 'NOTHING ELSE QUALIFIES

LOAN_CHANGE:
Application.ScreenUpdating = False
Cells(rw, cl + 1).Value = Cells(rw, cl).Value
rw_L = rw: rw_Sec = rw - 34
Cells(rw_L - 1, 17).Value = Cells(rw_L, 5).Value + Cells(rw_L, 8).Value +
Cells(rw_L, 11).Value + Cells(rw_L, 14).Value + Cells(rw_L, 17).Value
GoTo LVR_CHECK

RU_CHANGE:
Application.ScreenUpdating = False
rw_L = rw + 26: rw_Sec = rw - 8: cl = cl + 1

LVR_CHECK:
loan_total = Cells(rw_L, 5).Value + Cells(rw_L, 8).Value + Cells(rw_L,
11).Value + Cells(rw_L, 14).Value + Cells(rw_L, 17).Value
If (loan_total + Cells(rw_Sec + 4, 7)) / Cells(rw_Sec, 7) <= 0.8 Then:
Cells(rw_L - 3, 19).ClearContents: Cells(rw_L - 4, 19).ClearContents: GoTo
EXIT_SUB

GET_LMI:
Application.Run "PERSONAL.xls!WAV_DING"
answer = MsgBox("ARE YOU READY TO CALCULATE THE LMI PREMIUM YET?",
vbOKCancel + vbDefaultButton2 + vbQuestion, "CALCULATE LMI PREMIUM")
If answer = 2 Then: GoTo EXIT_SUB

Application.Calculation = xlCalculationManual
Cells(rw_L - 4, 19).ClearContents: Cells(rw_L - 3, 19).ClearContents
Application.Run "PERSONAL.xls!GET_LMI_APP", rw_L, cl

EXIT_SUB:
If Application.Calculation = xlCalculationManual Then:
Application.Calculation = xlCalculationAutomatic
If Application.EnableEvents = False Then: Application.EnableEvents = True
If Application.ScreenUpdating = False Then: Application.ScreenUpdating = True
End Sub

There are 3 main areas of execution"
one for LOAN_CHANGE and it works perfectly
one for RU_CHANGE and it works perfectly
one for CHANGE_LOAN_PRODUCT and it is a very difficult child indeed.

I use Select Case to narrow the field because the code is brief and very
specific.

I know that CHANGE_LOAN_PRODUCT startst becasue the first two Msgboxes come
up, but from the Target.value = 100 nothing happens. The most important line
to execute is 'Application.Run "PERSONAL.xls!RESET_REFINANCE" but I have
commented that out because it won't work from here (works perfectly from 5
click events however).

There is a selection change sub but the Select Case in there eleiminates any
confusion with these cells (commenting it and the click subs out makes no
difference). I've tried various other simple instructions where I have
Target.Value = 100 but nothing at all will go. I only put that particular
line in for debugging (100 is one of the values that can be returned by the
Public Function). After that I'm struggling. Regards, Brett