Can't get both codes to work when both are active

[Aaron DOJ]

Hello community of wizards,

I have two pieces of my code below that I can't seem to work together. The first part allows the user to clear a auto populated field from another worksheet in the workbook if they X a field they can manually put in the value they need to and if they uncheck the auto populated info comes back thanks to the inputted formula. The next one allows the user to select a location from a drop-down and then auto checks the value to a physical address again using info from a worksheet in the workbook. Both of them in the code and active only the top code works and the bottom one will no longer change any of the locations to addresses. If I inactivate either of them the other works just fine. If I put the bottom one above the top code I get 1004 error.

How can I get the two of these to work while both are active?

 Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) <> "W7" Then Exit Sub
If LCase(Target) = "x" Then
    'If "X" or "x" clear T7 and allow user to manually put in PCA
      Range("T7:V7").ClearContents
      MsgBox "Please enter your PCA in Section 3c"
Else
      Range("T7").Formula = "=VLOOKUP(O7,'PCA Look up'!K3:L166,2,FALSE)"
    'If "blank" put formula in T7
End If


If Target.Cells.Count > 1 Then GoTo exitHandler
If Target.Column = 24 And Target.Row = 7 Then
If Target.Value = "" Then GoTo exitHandler
    Application.EnableEvents = False
    Target.Value = Worksheets("Work Loc").Range("A1") _
        .Offset(Application.WorksheetFunction _
        .Match(Target.Value, Worksheets("Work Loc").Range("Names"), 0), 0)
End If


exitHandler:
    Application.EnableEvents = True
    Exit Sub


End Sub

-Aaron

 

[Eric W]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address(0, 0) = "W7" Then
    If Target.CountLarge > 1 Then Exit Sub
    If LCase(Target) = "x" Then
        'If "X" or "x" clear T7 and allow user to manually put in PCA
        Range("T7:V7").ClearContents
        MsgBox "Please enter your PCA in Section 3c"
    Else
        Range("T7").Formula = "=VLOOKUP(O7,'PCA Look up'!K3:L166,2,FALSE)"
        'If "blank" put formula in T7
    End If
    Exit Sub
End If


If Target.Address(0, 0) = "X7" Then
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo exitHandler:
    Target.Value = Worksheets("Work Loc").Range("A1") _
        .Offset(Application.WorksheetFunction _
        .Match(Target.Value, Worksheets("Work Loc").Range("Names"), 0), 0)
End If


exitHandler:
    Application.EnableEvents = True


End Sub

Your original second line:

If Target.Address(0, 0) <> "W7" Then Exit Sub

was bailing out of the entire routine if the target wasn't W7. You need to let it continue where the second part can check for the target address.

 

[ricardodiazjimenez]

Hi,

this should get you started.

Private Sub Worksheet_Change(ByVal Target As Range)


' Check if more than one cell is affected
If Target.Cells.Count > 1 Then GoTo exitHandler


Application.EnableEvents = False


Select Case Target.Address
    
    Case "$W$7"
    
        If LCase(Target) = "x" Then
            'If "X" or "x" clear T7 and allow user to manually put in PCA
              Range("T7:V7").ClearContents
              MsgBox "Please enter your PCA in Section 3c"
        Else
              Range("T7").Formula = "=VLOOKUP(O7,'PCA Look up'!K3:L166,2,FALSE)"
            'If "blank" put formula in T7
        End If
        
    Case "$X$7"
        ' Adjust this one according to you needs
        Target.Value = Worksheets("Work Loc").Range("A1") _
        .Offset(Application.WorksheetFunction _
        .Match(Target.Value, Worksheets("Work Loc").Range("Names"), 0), 0)
End Select


exitHandler:
    Application.EnableEvents = True
    Exit Sub
    
End Sub

 

[Aaron DOJ]

Thank you Eric, works like a charm

 

[Aaron DOJ]

Thank you Ricardo, I tried Eric's first and worked, I am going to use that one, I really appreciate you and your time
-Aaron