Create popup asking for User Input?

Question

Howdy howdy,

I'm working with various Excel documents, each one is different in row number.  One of the columns (O) has info for transport abbreviations.  Another (N) has the "long text" of those abbreviations. IE: N2 has "FOB Texas" and O2 has "FOB" (which is the correct abbreviation, I don't need "texas").

about 90% of column N is correct, however some are not.  I have some that say, for example, "truck to mill", and the abbreviation should be FOB.  I have a formula  to compare what the long text is and what it's corresponding abbreviation is.  However, as I mentioned, there are a few random ones that don't really match up to the standard abbreviation.

Here's the code I have to look at the long text and output its corresponding abbreviation:

=INDEX(Terms!$B$1:$B$11,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(Terms!$A$1:$A$11,Sheet1!N11)),),0))

Note: "Terms" is a sheet next to the main sheet with all of the info.  Here's the entry of "Terms":

free on   board	FOB
FOB	FOB
Free on truck	FOT
CFR	CFR

So, basically, that formula checks the column with long text for things like "free on board", "FOB", etc. and outputs its corresponding abbreviation in column B of Terms sheet.

I know I could go to Terms and add in the random "long text" ones that don't match up, but they're so inconsistent, so I would like to instead have a popup occur asking the user something like "What Term should 'Truck on Mill' be?" and the user will input (via typing) - OR select from choices, whichever is easier to script - "FOB" and then "FOB" will be inserted into its corresponding cell.

I have no idea if the above makes sense, but basically, if a cell value in column N has "#N/A", I would like a popup to ask "What is the Term for [value in adjacent cell] ?" and the user inputs something, and that value goes into the cell that previously had "#N/A".

Thanks for any help, and please let me know if some clarification would help.  :)

TL;DR: 

For all cells with "#N/A" in Column N, I would like a popup asking "What is the Term?" and then the user types in something, and then Excel enters that input into #N/A in column O.  OR if possible, simply replace the #N/A cell with the Input from the user.

Answer 1

A-mazing.

I have tried both scripts, the macro that I click to run, and the previous, which runs "automatically". 

The macro where I hit "play" to run works exactly like I want!!!

Question regarding the other:  When I drag the cell box thing (with that square in the corner) down from the cell directly above one with #N/A, the pop up box has the correct "long text", prompting me for the correct abbreviation.

However, if I drag down from a cell that's more than one above it, it seems to be giving me the incorrect "long text"...Here's the script:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim str As String

    Dim rng As Range, c As Range

    If Not Intersect(Target, Range("O:O")) Is Nothing Then

        Set rng = Intersect(Target, Range("O:O"))

        For Each c In rng

            If IsError(c.Value) Then

                If c.Value = CVErr(xlErrNA) Then

                    Application.EnableEvents = False

                    c.Select

                    str = InputBox("What should the abbreviation be?" & vbCr & vbCr _

                        & Cells(Target.Row, "N").Value _

                        , "What should this be?")

                    If str <> "" Then c.Value = str

                    Application.EnableEvents = True

                End If

            End If

        Next c

    End If

End Sub

edit: and in the Immediate Window, "?application.enableevents" returns "True"