Macro in a endless loop

G

Guest

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
 
B

Bernie Deitrick

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 said:
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
Click to expand...
 
G

Guest

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 said:
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
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

Thanks Bernie

Bernie Deitrick said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

Also, consider changing your if macro to a select case as at the bottom and
remove selections.

shtInput.Unprotect
select case Range("product").Value =
case= "Capital_Bonus_2" Then
Range("RiskC,GPerioid").Locked = True
Range("SolvPrem").Locked = False
case="next case"
case else
end select
shtInput.Protect



--
Don Guillett
SalesAid Software
(e-mail address removed)
Brad said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vb Assistance 1
Lock Range of Cells 1
Lost Protection Settings on Save 2
Suppress printing using button 1
How to extend the life of a variable from one Private Sub routine to another? 1
Macro problem with protecting worksheet........... 9
Adding formulas to rows using a Loop Macro 2
Select a Row where the cursor is active 4

Top