Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Chnage....still need help please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Chnage....still need help please
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. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Chnage....still need help please
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 procedu (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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Chnage....still need help please
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 "Dave Peterson" wrote: 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. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Chnage....still need help please
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. Brettjg wrote: 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 procedu (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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
chnage from average to sum(or count) | Excel Discussion (Misc queries) | |||
chnage pronounsaction in speech recongnition | Excel Programming | |||
chnage date from tuesday 4 January 2005 10:39.26 to 04/01/2005 | Excel Worksheet Functions | |||
shortcut to chnage color of selected cell in excel? | Excel Worksheet Functions |