Inputbox on cancel delete initial action that prompted

[Ace71425]

See code below

Private Sub Worksheet_Change(ByVal TARGET As Range)
     
    Dim sReason As String
     
    If TARGET.Cells.Count > 1 Then Exit Sub
     
    Application.ScreenUpdating = False
     
    If Not Intersect(TARGET, Range("A1:A500")) Is Nothing Then
        If TARGET.Value = "MISC" Then
            sReason = Application.InputBox("Reason for MISC?", Type:=2)
            TARGET.Offset(0, 4) = sReason
        End If
    End If
     
    
    For I = 1 To 2000
        If Range("A" & I) = "MISC" And Range("E" & I) = "FALSE" Then
            Range("A" & I & ":E" & I).ClearContents
        End If
    Next I
     
    Application.ScreenUpdating = True
    
End Sub

I have this code that whenever a user picks MISC from a drop down validation in the A column it prompts them for a reason for choosing it which then pastes that reason to the subsequent E column. However when I click cancel it just puts FALSE in the E column and leaves the MISC. I tried using this...

For I = 1 To 2000
        If Range("A" & I) = "MISC" And Range("E" & I) = "FALSE" Then
            Range("A" & I & ":E" & I).ClearContents
        End If
    Next I

to delete the A column if they hit cancel but it isn't working. I dont want the user to be able to cancel out and leave the MISC box there. I want to basically force them to enter a reason. Thanks in advance!

 

[iggydarsa]

Private Sub Worksheet_Change(ByVal TARGET As Range)
     
    Dim sReason As String
     
    If TARGET.Cells.Count > 1 Then Exit Sub
     
    Application.ScreenUpdating = False
     
    If Not Intersect(Target, Range("A1:A500")) Is Nothing Then
        sReason = False
        If Target.Value = "MISC" Then
            While sReason = False
                sReason = Application.InputBox("Reason for MISC?", Type:=2)
            Wend
            Target.Offset(0, 4) = sReason
        End If
    End If
     
    Application.ScreenUpdating = True
    
End Sub

 

[Ace71425]

That's a step in the right direction. But I would like for them to be able to select cancel and have it delete the MISC in A if they picked it on error where that code just makes the cancel button unavailable. Also if you hit ok without any text it comes up with an error and allows them to leave the MISC without a reason which doesnt force them to do so.

 

[iggydarsa]

If Not Intersect(Target, Range("A1:A500")) Is Nothing Then
    sReason = ""
    If Target.Value = "MISC" Then
        While sReason = ""
            sReason = Application.InputBox("Reason for MISC?", Type:=2)
            If sReason = False Then
                Target.ClearContents
            Else
                Target.Offset(0, 4) = sReason
            End If
        Wend
    End If
End If

 

[Ace71425]

Sorry to be a pain ...that code works when the cancel button is pushed but now it's not working when I enter text its coming up with an error and not pasting to E. also when I click ok with blank text it comes up with an error.

 

[iggydarsa]

Sorry to be a pain ...that code works when the cancel button is pushed but now it's not working when I enter text its coming up with an error and not pasting to E. also when I click ok with blank text it comes up with an error.

Do you mean the code is errorring out or it is showing #ERROR on the sheet?
If it is the code, can you please tell me the error message?

 

[Ace71425]

The cancel button cancels out and deletes MISC from A perfectly but when I hit OK with or without any text in the input box it comes up with "run time error 13 type mismatch"

 

[iggydarsa]

The cancel button cancels out and deletes MISC from A perfectly but when I hit OK with or without any text in the input box it comes up with "run time error 13 type mismatch"

I believe that is a datatype mismatch... Are you declaring sReason somewhere in the code with a specific data type? if so, change the type to Variant. Also, does Column E use data validation?