Add ActiveX Combo Box input to Named Range

Hello, Been reading up on my problem and cannot find an answer.

- I have name ranges on a sheet named "Data"
- As an example one of the ranges is named "Ship_Name"
- On another sheet named "User_Entry" I have an ActiveX combo box named "Ship_Name"Combi" pointing to the named range stated abover.

Problem:
I would like the user to be able to type in a value to the combo box if the desired selection is not there, then when the combo box looses focus, i would like a prompt to ask the user if he'd like to add the entry to the list for future use.

Here is my current code:

Private Sub Ship_Name_Combi_LostFocus()

Dim Reply As Long
Dim New_Text As String
Const quote As String = """"
Dim Test_Var As Range

On Error Resume Next

New_Text = Ship_Name_Combi.Text
Test_Var = Sheets("Data").Range("Ship_Name").Cells("Ship_Name").Rows.Count + 1
    If New_Text = "" Then Exit Sub
        If WorksheetFunction.CountIf(Combi_Rng, New_Text) = 0 Then
            Reply = MsgBox("Add " & quote & New_Text & quote & " to list for future use?", vbYesNo + vbQuestion)
            If Reply = vbYes Then
                Sheets("Data").Range("Ship_Name").Cells("Ship_Name").Rows.Count 1 = New_Text
            End If
        End If
End Sub

In the code above, everything works except the addition of the entry to the named list.

In addition, on a related problem, once I get this working, I'd like to convert it to a function because I will have several ActiveX Combo Box's on the User_Input sheet.
So far the function code would look like this:

Private Sub Ship_Name_Combi_LostFocus()

Call Append_List(Ship_Name_Combi.Text, Sheets("Data").Range("Ship_Name"))

End Sub

Sub Append_List(Combi_Val As String, Combi_Rng As Range)

Dim Reply As Long
Const quote As String = """"

On Error Resume Next

    If Combi_Val = "" Then Exit Sub
        If WorksheetFunction.CountIf(Combi_Rng, Combi_Val) = 0 Then
            Reply = MsgBox("Add " & quote & Combi_Val & quote & " to list for future use?", vbYesNo + vbQuestion)
            If Reply = vbYes Then
                Sheets("Data").Range("Combi_Rng").Cells("Combi_Rng").Rows.Count 1, 1 = Combi_Val
            End If
        End If
      
End Sub

Anyone that can help would be greatly appreciated.

Thank you!

Above post edited as requested

Try this. Also, never use an error statement until the code is working

 Option Explicit

Private Sub Ship_Name_Combi_LostFocus()

    Dim Reply As Long
    Dim New_Text As String
    Const quote As String = """"
    Dim NextRw As Long

    On Error Resume Next
    New_Text = Ship_Name_Combi.Text
    With Sheets("Data").Range("Ship_Name")
        NextRw = .Rows.Count + 1
        If New_Text = "" Then Exit Sub
        If WorksheetFunction.CountIf(Combi_Rng, New_Text) = 0 Then
            Reply = MsgBox("Add " & quote & New_Text & quote & _
                           " to list for future use?", vbYesNo + vbQuestion)
            If Reply = vbYes Then .Cells(1, NextRw) = New_Text
        End If
    End If
End With
On Error GoTo 0
End Sub

That works, but the entry is placed in the wrong cell, strangly it seems to be working with columns rathers than rows. I ran 3 different tests to confirm this.

Thank you for your help,
DM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

I can't find that combobox

I know that but none seem to be the one that you asked about!