| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in a endless loop
What I'm trying to do is to force a zero into cell c13 in a particular
worksheet. I have two macro - the first is always running in the background - and the second is called by the first. It doesn't seem to matter where I try to force the zero into cell c13 it forces the macro into the loop. Any help would be appreciated. The following macro is always running Sub Worksheet_Change(ByVal Target As Range) 'If Target.Address = Range("product").Address Then If Range("product").Value = "" Then shtInput.[button 17].Visible = False Exit Sub End If 'End If If Range("product").Value = "Capital_Bonus_2" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_5" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_7" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "GPA_5Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_9Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "Maximum_Solutions_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_Seminole_County" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "MY_Guaranteed_Solution_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = False Range("SolvPrem").Select Selection.Locked = True ' shtInput.Range("c13") = 0 If Range("GPeriod").Value = "" Then Exit Sub End If shtInput.Protect End If Module1.check_error End Sub The following macro is check_error Sub check_error() Dim sht As Worksheet shtCB2D.Visible = False shtCB2V.Visible = False shtMAX2D.Visible = False shtMAX2V.Visible = False shtEH5D.Visible = False shtEH5V.Visible = False shtEH7D.Visible = False shtEH7V.Visible = False shtGPA5D.Visible = False shtGPAV.Visible = False shtGPA9D.Visible = False shtGPAV.Visible = False shtGPASC.Visible = False shtGPAV.Visible = False shtMYGSD.Visible = False shtMYGSV.Visible = False shtAgent.Visible = False If Range("product").Value = "MY_Guaranteed_Solution_II" Then ' shtInput.Range("c13") = 0 End If For Each sht In Worksheets ' sht.Visible = True Next If shtInpInfo.Range("valid").Value < 0 Then shtInput.[button 17].Visible = False Beep End If If shtInpInfo.Range("valid").Value = 0 Then shtInput.[button 17].Visible = True shtInput.[button 248].Visible = True Select Case Range("product").Value Case Is = "Capital_Bonus_2" shtCB2D.Visible = True shtCB2V.Visible = True Case Is = "Maximum_Solutions_II" shtMAX2D.Visible = True shtMAX2V.Visible = True Case Is = "Expanding_Horizon_5" shtEH5D.Visible = True shtEH5V.Visible = True Case Is = "Expanding_Horizon_7" shtEH7D.Visible = True shtEH7V.Visible = True Case Is = "GPA_5Yr" shtGPA5D.Visible = True shtGPAV.Visible = True Case Is = "GPA_9Yr" shtGPA9D.Visible = True shtGPAV.Visible = True Case Is = "GPA_Seminole_County" shtGPASC.Visible = True shtGPAV.Visible = True Case Is = "MY_Guaranteed_Solution_II" shtMYGSD.Visible = True shtMYGSV.Visible = True shtInput.[button 248].Visible = False Case Else MsgBox "Not a valid plan" End Select End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro in a endless loop
A couple of things... You are using sheet change event code and within the
routine you are changing the sheet causing a loop. This line: shtInput.Range("c13") = 0 changes the sheet and forces the routine to run again. Try adding this Application.EnableEvents = false shtInput.Range("c13") = 0 Application.Enable Events = True 'this line is critical When using this code make absoluetely sure to re-enable the events when you are done. You are making selections in your code that you don't need to. For example change Range("RiskC").Select Selection.Locked = True to Range("RiskC").Locked = True -- HTH... Jim Thomlinson "Brad" wrote: What I'm trying to do is to force a zero into cell c13 in a particular worksheet. I have two macro - the first is always running in the background - and the second is called by the first. It doesn't seem to matter where I try to force the zero into cell c13 it forces the macro into the loop. Any help would be appreciated. The following macro is always running Sub Worksheet_Change(ByVal Target As Range) 'If Target.Address = Range("product").Address Then If Range("product").Value = "" Then shtInput.[button 17].Visible = False Exit Sub End If 'End If If Range("product").Value = "Capital_Bonus_2" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_5" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_7" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "GPA_5Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_9Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "Maximum_Solutions_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_Seminole_County" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "MY_Guaranteed_Solution_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = False Range("SolvPrem").Select Selection.Locked = True ' shtInput.Range("c13") = 0 If Range("GPeriod").Value = "" Then Exit Sub End If shtInput.Protect End If Module1.check_error End Sub The following macro is check_error Sub check_error() Dim sht As Worksheet shtCB2D.Visible = False shtCB2V.Visible = False shtMAX2D.Visible = False shtMAX2V.Visible = False shtEH5D.Visible = False shtEH5V.Visible = False shtEH7D.Visible = False shtEH7V.Visible = False shtGPA5D.Visible = False shtGPAV.Visible = False shtGPA9D.Visible = False shtGPAV.Visible = False shtGPASC.Visible = False shtGPAV.Visible = False shtMYGSD.Visible = False shtMYGSV.Visible = False shtAgent.Visible = False If Range("product").Value = "MY_Guaranteed_Solution_II" Then ' shtInput.Range("c13") = 0 End If For Each sht In Worksheets ' sht.Visible = True Next If shtInpInfo.Range("valid").Value < 0 Then shtInput.[button 17].Visible = False Beep End If If shtInpInfo.Range("valid").Value = 0 Then shtInput.[button 17].Visible = True shtInput.[button 248].Visible = True Select Case Range("product").Value Case Is = "Capital_Bonus_2" shtCB2D.Visible = True shtCB2V.Visible = True Case Is = "Maximum_Solutions_II" shtMAX2D.Visible = True shtMAX2V.Visible = True Case Is = "Expanding_Horizon_5" shtEH5D.Visible = True shtEH5V.Visible = True Case Is = "Expanding_Horizon_7" shtEH7D.Visible = True shtEH7V.Visible = True Case Is = "GPA_5Yr" shtGPA5D.Visible = True shtGPAV.Visible = True Case Is = "GPA_9Yr" shtGPA9D.Visible = True shtGPAV.Visible = True Case Is = "GPA_Seminole_County" shtGPASC.Visible = True shtGPAV.Visible = True Case Is = "MY_Guaranteed_Solution_II" shtMYGSD.Visible = True shtMYGSV.Visible = True shtInput.[button 248].Visible = False Case Else MsgBox "Not a valid plan" End Select End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro in a endless loop
Brad,
Use Application.EnableEvents = False at the top of each of your macros/event codes, and at the bottom (or before any exit point), use: Application.EnableEvents = True HTH, Bernie MS Excel MVP "Brad" wrote in message ... What I'm trying to do is to force a zero into cell c13 in a particular worksheet. I have two macro - the first is always running in the background - and the second is called by the first. It doesn't seem to matter where I try to force the zero into cell c13 it forces the macro into the loop. Any help would be appreciated. The following macro is always running Sub Worksheet_Change(ByVal Target As Range) 'If Target.Address = Range("product").Address Then If Range("product").Value = "" Then shtInput.[button 17].Visible = False Exit Sub End If 'End If If Range("product").Value = "Capital_Bonus_2" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_5" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_7" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "GPA_5Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_9Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "Maximum_Solutions_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_Seminole_County" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "MY_Guaranteed_Solution_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = False Range("SolvPrem").Select Selection.Locked = True ' shtInput.Range("c13") = 0 If Range("GPeriod").Value = "" Then Exit Sub End If shtInput.Protect End If Module1.check_error End Sub The following macro is check_error Sub check_error() Dim sht As Worksheet shtCB2D.Visible = False shtCB2V.Visible = False shtMAX2D.Visible = False shtMAX2V.Visible = False shtEH5D.Visible = False shtEH5V.Visible = False shtEH7D.Visible = False shtEH7V.Visible = False shtGPA5D.Visible = False shtGPAV.Visible = False shtGPA9D.Visible = False shtGPAV.Visible = False shtGPASC.Visible = False shtGPAV.Visible = False shtMYGSD.Visible = False shtMYGSV.Visible = False shtAgent.Visible = False If Range("product").Value = "MY_Guaranteed_Solution_II" Then ' shtInput.Range("c13") = 0 End If For Each sht In Worksheets ' sht.Visible = True Next If shtInpInfo.Range("valid").Value < 0 Then shtInput.[button 17].Visible = False Beep End If If shtInpInfo.Range("valid").Value = 0 Then shtInput.[button 17].Visible = True shtInput.[button 248].Visible = True Select Case Range("product").Value Case Is = "Capital_Bonus_2" shtCB2D.Visible = True shtCB2V.Visible = True Case Is = "Maximum_Solutions_II" shtMAX2D.Visible = True shtMAX2V.Visible = True Case Is = "Expanding_Horizon_5" shtEH5D.Visible = True shtEH5V.Visible = True Case Is = "Expanding_Horizon_7" shtEH7D.Visible = True shtEH7V.Visible = True Case Is = "GPA_5Yr" shtGPA5D.Visible = True shtGPAV.Visible = True Case Is = "GPA_9Yr" shtGPA9D.Visible = True shtGPAV.Visible = True Case Is = "GPA_Seminole_County" shtGPASC.Visible = True shtGPAV.Visible = True Case Is = "MY_Guaranteed_Solution_II" shtMYGSD.Visible = True shtMYGSV.Visible = True shtInput.[button 248].Visible = False Case Else MsgBox "Not a valid plan" End Select End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro in a endless loop
Thanks Bernie
"Bernie Deitrick" wrote: Brad, Use Application.EnableEvents = False at the top of each of your macros/event codes, and at the bottom (or before any exit point), use: Application.EnableEvents = True HTH, Bernie MS Excel MVP "Brad" wrote in message ... What I'm trying to do is to force a zero into cell c13 in a particular worksheet. I have two macro - the first is always running in the background - and the second is called by the first. It doesn't seem to matter where I try to force the zero into cell c13 it forces the macro into the loop. Any help would be appreciated. The following macro is always running Sub Worksheet_Change(ByVal Target As Range) 'If Target.Address = Range("product").Address Then If Range("product").Value = "" Then shtInput.[button 17].Visible = False Exit Sub End If 'End If If Range("product").Value = "Capital_Bonus_2" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_5" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_7" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "GPA_5Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_9Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "Maximum_Solutions_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_Seminole_County" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "MY_Guaranteed_Solution_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = False Range("SolvPrem").Select Selection.Locked = True ' shtInput.Range("c13") = 0 If Range("GPeriod").Value = "" Then Exit Sub End If shtInput.Protect End If Module1.check_error End Sub The following macro is check_error Sub check_error() Dim sht As Worksheet shtCB2D.Visible = False shtCB2V.Visible = False shtMAX2D.Visible = False shtMAX2V.Visible = False shtEH5D.Visible = False shtEH5V.Visible = False shtEH7D.Visible = False shtEH7V.Visible = False shtGPA5D.Visible = False shtGPAV.Visible = False shtGPA9D.Visible = False shtGPAV.Visible = False shtGPASC.Visible = False shtGPAV.Visible = False shtMYGSD.Visible = False shtMYGSV.Visible = False shtAgent.Visible = False If Range("product").Value = "MY_Guaranteed_Solution_II" Then ' shtInput.Range("c13") = 0 End If For Each sht In Worksheets ' sht.Visible = True Next If shtInpInfo.Range("valid").Value < 0 Then shtInput.[button 17].Visible = False Beep End If If shtInpInfo.Range("valid").Value = 0 Then shtInput.[button 17].Visible = True shtInput.[button 248].Visible = True Select Case Range("product").Value Case Is = "Capital_Bonus_2" shtCB2D.Visible = True shtCB2V.Visible = True Case Is = "Maximum_Solutions_II" shtMAX2D.Visible = True shtMAX2V.Visible = True Case Is = "Expanding_Horizon_5" shtEH5D.Visible = True shtEH5V.Visible = True Case Is = "Expanding_Horizon_7" shtEH7D.Visible = True shtEH7V.Visible = True Case Is = "GPA_5Yr" shtGPA5D.Visible = True shtGPAV.Visible = True Case Is = "GPA_9Yr" shtGPA9D.Visible = True shtGPAV.Visible = True Case Is = "GPA_Seminole_County" shtGPASC.Visible = True shtGPAV.Visible = True Case Is = "MY_Guaranteed_Solution_II" shtMYGSD.Visible = True shtMYGSV.Visible = True shtInput.[button 248].Visible = False Case Else MsgBox "Not a valid plan" End Select End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro in a endless loop
Thanks Jim for the answer - I really appreciate all the input it has made the
workbook much better. Made the other change recommended as well. Thanks for not mentioning range names - I wanted to get the macro working and then add the range names. "Bernie Deitrick" wrote: Brad, Use Application.EnableEvents = False at the top of each of your macros/event codes, and at the bottom (or before any exit point), use: Application.EnableEvents = True HTH, Bernie MS Excel MVP "Brad" wrote in message ... What I'm trying to do is to force a zero into cell c13 in a particular worksheet. I have two macro - the first is always running in the background - and the second is called by the first. It doesn't seem to matter where I try to force the zero into cell c13 it forces the macro into the loop. Any help would be appreciated. The following macro is always running Sub Worksheet_Change(ByVal Target As Range) 'If Target.Address = Range("product").Address Then If Range("product").Value = "" Then shtInput.[button 17].Visible = False Exit Sub End If 'End If If Range("product").Value = "Capital_Bonus_2" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_5" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_7" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "GPA_5Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_9Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "Maximum_Solutions_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_Seminole_County" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "MY_Guaranteed_Solution_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = False Range("SolvPrem").Select Selection.Locked = True ' shtInput.Range("c13") = 0 If Range("GPeriod").Value = "" Then Exit Sub End If shtInput.Protect End If Module1.check_error End Sub The following macro is check_error Sub check_error() Dim sht As Worksheet shtCB2D.Visible = False shtCB2V.Visible = False shtMAX2D.Visible = False shtMAX2V.Visible = False shtEH5D.Visible = False shtEH5V.Visible = False shtEH7D.Visible = False shtEH7V.Visible = False shtGPA5D.Visible = False shtGPAV.Visible = False shtGPA9D.Visible = False shtGPAV.Visible = False shtGPASC.Visible = False shtGPAV.Visible = False shtMYGSD.Visible = False shtMYGSV.Visible = False shtAgent.Visible = False If Range("product").Value = "MY_Guaranteed_Solution_II" Then ' shtInput.Range("c13") = 0 End If For Each sht In Worksheets ' sht.Visible = True Next If shtInpInfo.Range("valid").Value < 0 Then shtInput.[button 17].Visible = False Beep End If If shtInpInfo.Range("valid").Value = 0 Then shtInput.[button 17].Visible = True shtInput.[button 248].Visible = True Select Case Range("product").Value Case Is = "Capital_Bonus_2" shtCB2D.Visible = True shtCB2V.Visible = True Case Is = "Maximum_Solutions_II" shtMAX2D.Visible = True shtMAX2V.Visible = True Case Is = "Expanding_Horizon_5" shtEH5D.Visible = True shtEH5V.Visible = True Case Is = "Expanding_Horizon_7" shtEH7D.Visible = True shtEH7V.Visible = True Case Is = "GPA_5Yr" shtGPA5D.Visible = True shtGPAV.Visible = True Case Is = "GPA_9Yr" shtGPA9D.Visible = True shtGPAV.Visible = True Case Is = "GPA_Seminole_County" shtGPASC.Visible = True shtGPAV.Visible = True Case Is = "MY_Guaranteed_Solution_II" shtMYGSD.Visible = True shtMYGSV.Visible = True shtInput.[button 248].Visible = False Case Else MsgBox "Not a valid plan" End Select End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro in a endless loop
Your code is looking really good. A long way from where you started. One
thing that I see it that you have alot of Exit Subs, which are not a problem in and of themselves but be careful about reseting application level settings when you have exit subs. It is easy to exit sub prior to resetting the application settings and then everything falls apart after that. Just make darn sure you have done all o fyour housekeeping prior to exiting the sub. Aditionally with setting application level setting it is a good idea to add an error handler... -- HTH... Jim Thomlinson "Brad" wrote: Thanks Jim for the answer - I really appreciate all the input it has made the workbook much better. Made the other change recommended as well. Thanks for not mentioning range names - I wanted to get the macro working and then add the range names. "Bernie Deitrick" wrote: Brad, Use Application.EnableEvents = False at the top of each of your macros/event codes, and at the bottom (or before any exit point), use: Application.EnableEvents = True HTH, Bernie MS Excel MVP "Brad" wrote in message ... What I'm trying to do is to force a zero into cell c13 in a particular worksheet. I have two macro - the first is always running in the background - and the second is called by the first. It doesn't seem to matter where I try to force the zero into cell c13 it forces the macro into the loop. Any help would be appreciated. The following macro is always running Sub Worksheet_Change(ByVal Target As Range) 'If Target.Address = Range("product").Address Then If Range("product").Value = "" Then shtInput.[button 17].Visible = False Exit Sub End If 'End If If Range("product").Value = "Capital_Bonus_2" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_5" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "Expanding_Horizon_7" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False shtInput.Protect End If If Range("product").Value = "GPA_5Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_9Yr" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "Maximum_Solutions_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "GPA_Seminole_County" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = False Range("GPeriod").Select Selection.Locked = True Range("SolvPrem").Select Selection.Locked = False If Range("RiskC").Value = "" Then Exit Sub End If shtInput.Protect End If If Range("product").Value = "MY_Guaranteed_Solution_II" Then shtInput.Unprotect Range("RiskC").Select Selection.Locked = True Range("GPeriod").Select Selection.Locked = False Range("SolvPrem").Select Selection.Locked = True ' shtInput.Range("c13") = 0 If Range("GPeriod").Value = "" Then Exit Sub End If shtInput.Protect End If Module1.check_error End Sub The following macro is check_error Sub check_error() Dim sht As Worksheet shtCB2D.Visible = False shtCB2V.Visible = False shtMAX2D.Visible = False shtMAX2V.Visible = False shtEH5D.Visible = False shtEH5V.Visible = False shtEH7D.Visible = False shtEH7V.Visible = False shtGPA5D.Visible = False shtGPAV.Visible = False shtGPA9D.Visible = False shtGPAV.Visible = False shtGPASC.Visible = False shtGPAV.Visible = False shtMYGSD.Visible = False shtMYGSV.Visible = False shtAgent.Visible = False If Range("product").Value = "MY_Guaranteed_Solution_II" Then ' shtInput.Range("c13") = 0 End If For Each sht In Worksheets ' sht.Visible = True Next If shtInpInfo.Range("valid").Value < 0 Then shtInput.[button 17].Visible = False Beep End If If shtInpInfo.Range("valid").Value = 0 Then shtInput.[button 17].Visible = True shtInput.[button 248].Visible = True Select Case Range("product").Value Case Is = "Capital_Bonus_2" shtCB2D.Visible = True shtCB2V.Visible = True Case Is = "Maximum_Solutions_II" shtMAX2D.Visible = True shtMAX2V.Visible = True Case Is = "Expanding_Horizon_5" shtEH5D.Visible = True shtEH5V.Visible = True Case Is = "Expanding_Horizon_7" shtEH7D.Visible = True shtEH7V.Visible = True Case Is = "GPA_5Yr" shtGPA5D.Visible = True shtGPAV.Visible = True Case Is = "GPA_9Yr" shtGPA9D.Visible = True shtGPAV.Visible = True Case Is = "GPA_Seminole_County" shtGPASC.Visible = True shtGPAV.Visible = True Case Is = "MY_Guaranteed_Solution_II" shtMYGSD.Visible = True shtMYGSV.Visible = True shtInput.[button 248].Visible = False Case Else MsgBox "Not a valid plan" End Select End If End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro in a endless loop
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| endless loop - using input box | Excel Programming | |||
| Endless loop | Excel Programming | |||
| endless loop help | Excel Programming | |||
| Endless loop? | Excel Programming | |||
| Interrupting an endless loop | Excel Programming | |||
Hybrid Mode
