Worksheet Change event not running a macro

[Brettjg]

Hi, I have a worksheet cnage event which goes a certain distance and then
does nothing. The part of the code is:

rw = Target.Row
cl = Target.Column
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"

The first Msgbox presents me with the correct values but then nothing at all
happens, and it doesn't matter if I have events on/off and calc on/off. I
know it doesn't run the RESET_REFINANCE macro because I've put an immediate
Msgbox in there which doesn't fire, and curiously it doesn't even debug if I
tell it to run a non-existent macro. The second Msgbox box "RETURN from
RESET_REFINANCE" doesn't fire either.

I do have a Private Sub APP1_reset_Click() but at the moment I've commented
it out to see if it was causing some interference. I also have a selection
change macro but commenting that out doesn't make any difference either.

At that point I need...............HELLLLLLLLLP please! Regards, Brett

 

[Gary''s Student]

Can't see enough of your code. However make sure:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
' your code
Application.EnableEvents = True
End Sub

 

[joel]

Make sure you have the Stop on Error setting in excel set properly so it will
display error messages

from VBA menu

Tools - Options - General - Error Trapping - Stop On all Errors

 

[Brettjg]

Hi GS, thanks for replying. Well there isn't much code:

rw = Target.Row
cl = Target.Column
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"

is the section in question. The other sections works perfectly. It just
doesn't do anything about the macro onwards.

 

[joel]

You are running a macro in the personal.xls that may be failing. Add some
debug messages like this

rw = Target.Row
cl = Target.Column
spt_abs = Cells(rw + 1, cl + 1).Value
MsgBox "A = " & (rw + 25) / 39 & " Target.Value = " & Target.Value & "
SPLIT = " & (cl - 1) / 3
msgbox("Running Refinance")
Application.Run "PERSONAL.xls!RESET_REFINANCE", (rw + 25) / 39,
msgbox("finished Running Refinance")
Target.Value, (cl - 1) / 3
MsgBox "RETURN from RESET_REFINANCE"

 

[Brettjg]

Hi Joel, thanks for your responses. The macro I'm calling works beautiffuly
from the
Private Sub APP1_reset_Click(). It's quite bizarre. Other parts of the
worksheet change macro work perfectly. In debugging I put a msgbox as the
first line in the macro I'm calling and that doesn't come up either. Even if
I deliberately call a macro that does NOT exist, it doesn't debug. That is to
say that it stops after the first msgbox any doesn't do anything after that.
Not sure if this makes it clearer to you or not. Brett

 

[joel]

The problem may be the comma after the 39 in your calling staement. I think
excel is stopping becaue there is something wrong with the parameter list.
You could try putting a break point (F9) on the 1st line of the macro inside
the personnal.xls file and then stepping through the code with F8.

 

[Brettjg]

Hi Joel, I don't know what happened there with the cut & paste. There
actually is data after the comma:
rw = Target.Row
cl = Target.Column
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"


and you are quite right, it wouldn't work like that...BUT it doesn't even do
the calling - remember if I call a NON-EXISTING macro it doesn't go to debug.
I'll paste the whole macro here - important to note that ALL other sections
work perfectly:


Private Sub Worksheet_Change(ByVal Target As Range)
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: Application.EnableEvents = False:
GoTo CHANGE_LOAN_PRODUCT
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
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"

' REFINANCE_CHANGE_SUPERIOR_LEDGER(A_num, spt_abs, curr_ledg, fx_ledg, UorD)
'If Left(Cells(rw, cl + 1), 3) = "MAC" Or Left(Cells(rw, cl + 1), 3) = "OGN"
Then: Application.Run "PERSONAL.xls!REFINANCE_CHANGE_SUPERIOR_LEDGER",
spt_abs, 0, 0, "U"
'If countRX > Range("RX.count").Value Then: Application.Run
"PERSONAL.xls!REFINANCE_CHANGE_SUPERIOR_LEDGER", spt_abs, 0, 0, "D"
'If countRX < Range("RX.count").Value Then: Application.Run
"PERSONAL.xls!REFINANCE_CHANGE_SUPERIOR_LEDGER", spt_abs, 0, 0, "U"
'If countYX > Range("YX.count").Value Then: Application.Run
"PERSONAL.xls!REFINANCE_CHANGE_SUPERIOR_LEDGER", spt_abs, 0, 0, "D"
'If countYX < Range("YX.count").Value Then: Application.Run
"PERSONAL.xls!REFINANCE_CHANGE_SUPERIOR_LEDGER", spt_abs, 0, 0, "U"

GoTo EXIT_SUB 'NOTHING ELSE QUALIFIES

LOAN_CHANGE:
'Application.Calculation = xlCalculationManual
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

 

[joel]

Again, there is something wrong with the parameter list or you are calling a
function instead of a sub. Ff the macro is a function yo do't have any
return parameter which will cause a STACK problem. Excel will also have a
problem is the parameters are not the same type.

when you have a macro in the same workbook excel is able to determine that
the parameters are not the same and will report the error. When the calling
macro is in a different workbook this error checking is not always possible.

 

[egun]

This line:

SPLIT = " & (cl - 1) / 3

seems to be missing a second double quote (").

HTH,

Eric

 

[Brettjg]

Hi Eric, no it's there - just the way it pasted into this post. What you are
referring to is actually the end of the previous line. But thanks anyway