Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
Can't see enough of your code. However make su
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200843 "Brettjg" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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 "Gary''s Student" wrote: Can't see enough of your code. However make su Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200843 "Brettjg" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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. "Gary''s Student" wrote: Can't see enough of your code. However make su Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200843 "Brettjg" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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" "joel" wrote: 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 "Gary''s Student" wrote: Can't see enough of your code. However make su Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200843 "Brettjg" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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" wrote: 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" "joel" wrote: 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 "Gary''s Student" wrote: Can't see enough of your code. However make su Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200843 "Brettjg" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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" wrote: 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" wrote: 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" "joel" wrote: 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 "Gary''s Student" wrote: Can't see enough of your code. However make su Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200843 "Brettjg" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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" wrote: 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" wrote: 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" wrote: 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" "joel" wrote: 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 "Gary''s Student" wrote: Can't see enough of your code. However make su Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200843 "Brettjg" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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. "Brettjg" wrote: 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" wrote: 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" wrote: 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" wrote: 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" "joel" wrote: 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 "Gary''s Student" wrote: Can't see enough of your code. However make su Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200843 "Brettjg" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
This line:
SPLIT = " & (cl - 1) / 3 seems to be missing a second double quote ("). HTH, Eric |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change event not running a macro
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 "egun" wrote: This line: SPLIT = " & (cl - 1) / 3 seems to be missing a second double quote ("). HTH, Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running of Worksheet Change Macro breaks undo functionality. | Excel Discussion (Misc queries) | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
How to keep ONKey's macro on Worksheet Selection Change Event | Excel Programming | |||
Copy Sheets minus Worksheet Change Event code & Macro Buttons | Excel Programming |