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?
-Aaron
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