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

hensleyj

New Member
Joined
Apr 2, 2012
Messages
39
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 :)

Code:
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;

Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,297
Members
417,135
Latest member
zeusmining

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top