Replacing delete with a value

[mecerrato]

I have code that executes an autofilter to show data based on the value in cell d6. However, when the user hits the delete key the autofilter shows now data. If the user hits delete on d6 I would like to make the value of the cell 0.
Is this possible?

Code when there is a change:

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheets("Main").Unprotect Password:="Mortgage1"
If Target.Address = "$B$6" Then
    If Range("d6").Value > 0 Then
    Call LO_and_Rate_Name_Change
    Else
    Call LO_Name_Change
End If
End If

If Target.Address = "$B$7" Then

    Call Borrower_Name_Change
End If

If Target.Address = "$B$8" Then
    Call Rate_Name_Change
End If

If Target.Address = "$D$6" Then
    If Range("b6").Value = "" Then
    MsgBox "Please Select an LO", vbOKCancel
    Range("b6").Select
    Else
    Call LO_and_Rate_Name_Change
End If
End If

End Sub

Code executed when there is a change in cell d6

Sub LO_and_Rate_Name_Change() 'Borrower Dropdown list
ActiveSheet.Unprotect
Dim mysht As Worksheet
Dim myDropDown As Shape
Dim myVal As String
Dim myVal_LO As String
Dim r As Range
Set r = ActiveSheet.Range("$a$21:$v$10000")
Set mysht = ThisWorkbook.Worksheets("Data")
myVal = ActiveSheet.Range("$d$6").Value
myVal_LO = ActiveSheet.Range("$b$6").Value
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
    ActiveSheet.Range("$a$21:$v$10000").AutoFilter Field:=6, Criteria1:=">" & myVal
    ActiveSheet.Range("$a$21:$v$10000").AutoFilter Field:=2, Criteria1:=myVal_LO
    Range(Cells(r.Rows.Count, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True
    Range("$a$21:$v$10000").Sort Key1:=Range("c21"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
ActiveWindow.ScrollColumn = 1
Application.EnableEvents = False
Range("b7:b8").Value = Null

Application.EnableEvents = True
'Range("A11").Select
End Sub

 

[Alex Blakenburg]

I am probably missing something something here but don't you just need to add the line below:

    If Target.Address = "$D$6" Then
        If Target.Value = "" Then Target.Value = 0  ' if D6 deleted replace blank with 0
        
        If Range("b6").Value = "" Then
            MsgBox "Please Select an LO", vbOKCancel
            Range("b6").Select
        Else
            Call LO_and_Rate_Name_Change
    End If

 

[mecerrato]

I am probably missing something something here but don't you just need to add the line below:

    If Target.Address = "$D$6" Then
        If Target.Value = "" Then Target.Value = 0  ' if D6 deleted replace blank with 0
       
        If Range("b6").Value = "" Then
            MsgBox "Please Select an LO", vbOKCancel
            Range("b6").Select
        Else
            Call LO_and_Rate_Name_Change
    End If

sorry sometimes it is right in front of you and you can't see it, thanks

 

[Alex Blakenburg]

No problem. Glad I could help.