COMBINE 2 working - worksheet change event codes in to 1.

[hensleyj]

Hey everyone,

I am working with some macro's in VBA and i am a little stuck. VBA is still new to me and im not entirely sure what im doing. I was given help with code for 2 different worksheet changes, but now i have 1 sheet, where i need to have both the codes working together (combined). This is where i am lost. Below are the 2 separate codes i have which are working....can anyone show me how to combine them both to make em still work?

From what i understand, you cannot have more than 1 sub worksheet_change?

Anyway, any help would be much appreciated

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Address(0, 0) = "G7" Then
        Application.EnableEvents = False
        If Target.Value = "A - B - D" Then
            Worksheets("PD Payment Calculator").Unprotect
            Range("G148").Validation.Modify Type:=xlValidateInputOnly
            Range("G148").Select
            ActiveCell.FormulaR1C1 = "A - B - D"
            Range("G148").Locked = True
            Worksheets("PD Payment Calculator").Protect
            Range("G7").Select
        
        ElseIf Target.Value = "Occupation F: 25% of Benefit" Then
            Worksheets("PD Payment Calculator").Unprotect
            Range("G148").Validation.Modify Type:=xlValidateInputOnly
            Range("G148").Select
            ActiveCell.FormulaR1C1 = "C + D (Cannot Exceed 30% of PDI)"
            Range("G148").Locked = True
            Worksheets("PD Payment Calculator").Protect
            Range("G7").Select
        Else
            Worksheets("PD Payment Calculator").Unprotect
            Range("G148").Validation.Modify Type:=xlValidateList, Formula1:="C - D, A - B - D,( 0.75 ) x A ) - D,C + D (Cannot Exceed 30% of PDI),C + D (Cannot Exceed 55% of PDI),C + D (Cannot Exceed 65% of PDI),C + D (Cannot Exceed 75% of PDI),C + D (Cannot Exceed 100% of PDI)"
        Range("G148").Locked = False
        End If
        Application.EnableEvents = True
        End If
End Sub

Here is the second one, which is setup with cases to trigger a multitude of macros;

Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("G7,G11,G13,G17,g39,G41,G70,G88,G110")) Is Nothing Then
    Select Case Target.Address(0, 0)
    
    Case "G7"
        If Target.Value > "" Then Call PolicyCheckError
    Case "G11"
        If Target.Value > "" Then Call DatetoError
    Case "G17"
        If Target.Value > "" Then Call NegBenefitError
    Case "G13"
        If Target.Value > "" Then Call DatetoError
    Case "G39"
        If Target.Value > "" Then Call EBRCheckError
    Case "G41"
        If Target.Value > "" Then Call EBRCheckError
    Case "G43"
        If Target.Value > "" Then Call EBRChange
    Case "G70"
        If Target.Value > "" Then Call Adjustment
        If Target.Value = "" Then Call NoAdjustment
    Case "G88"
        If Target.Value = "First SGC Payment" Then Call SGCInitial
        If Target.Value = "Ongoing SGC Payment" Then Call SGCOngoing
        If Target.Value = "" Then Call SGCInitial
        End Select
End If
End Sub

 

[mole999]

you could have a test in the macro to run one or other portion, but you can't have two identically named subs. you might consider just having the switches in the worksheet change, and then call one or other sub

 

[hensleyj]

Thanks heaps, your suggestion made me realize what i was doing wrong