Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Aug 2018
    Location
    Oregon
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

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

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,728
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Can't get both codes to work when both are active

    Try:

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

    Code:
    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    May 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can't get both codes to work when both are active

    Hi,

    this should get you started.

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

  4. #4
    New Member
    Join Date
    Aug 2018
    Location
    Oregon
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can't get both codes to work when both are active

    Thank you Eric, works like a charm

  5. #5
    New Member
    Join Date
    Aug 2018
    Location
    Oregon
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can't get both codes to work when both are active

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •