User form has cells that need autocomplete for common values

I need to have a list that autocomplete values into a cell. With the way the form is setup I can't list and hide them at the bottom because the comments cell is empty until the end.

Is there a way to make a dynamic list within a cell that makes autocomplete work within an adjacent cell?

One cell example is Name. If someone enters their name and it has been entered before it should autocomplete. If it is a new name, it should store it for the next time.

I made a macro to do this and put spaces in all the empty cells in the column to make them "not empty". Unfortunately the form will have things that aren't filled out already that create an empty cell.

Sub WhiteRabbit()
'
'Macro WhiteRabbit

'
    'Turn off screen updating and unprotect worksheet
    Application.ScreenUpdating = False
    Sheets("Entry Form").Select
    ActiveSheet.Unprotect
'**********++++++++++============BEGIN GRABBING INFO============++++++++++**********
'COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B
'----------------COLUMN B Grab info----------------
    Sheets("Entry Form").Select
    Range("B7").Select '(Grab B7 Tech Name)
    Selection.Copy
'Add to Auto List Column B
    Sheets("Entry Form").Select
    Range("B25").Select
    Selection.End(xlDown).Select 'Go to last item
        ActiveCell.Offset(1, 0).Range("A1").Select 'then one more to the next blank spot
    'Paste value with invisible formatting
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
        'Selection.NumberFormat = ";;;"
'----------------END COLUMN B Grab info-------------

'============Remove Duplicates from Column B============
Range("B25").End(xlDown).Select
ActiveSheet.Range("B25", Range("B25").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo

'Add color back to Any that had it removed
Range(Selection, Selection.End(xlUp)).Select
    'Range(Selection, Selection.End(xlUp)).Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Selection.NumberFormat = ";;;"
'============End Remove Duplicates from Column B=========
'COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B COLUMN B

'COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D
'----------------COLUMN D Grab info----------------
    Sheets("Entry Form").Select
    Range("D13").Select '(Grab D13 UNIT)
    Selection.Copy
'Add to Auto List Column D
    Sheets("Entry Form").Select
    Range("D25").Select
    Selection.End(xlDown).Select 'Go to last item
        ActiveCell.Offset(1, 0).Range("A1").Select 'then one more to the next blank spot
    'Paste value with invisible formatting
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
        'Selection.NumberFormat = ";;;"
'----------------END COLUMN D Grab info-------------

'============Remove Duplicates from Column D============
Range("D25").End(xlDown).Select
ActiveSheet.Range("D25", Range("D25").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo

'Add color back to Any that had it removed
Range(Selection, Selection.End(xlUp)).Select
    'Range(Selection, Selection.End(xlUp)).Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Selection.NumberFormat = ";;;"
'============End Remove Duplicates from Column D=========
'COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D COLUMN D


'COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F
'----------------COLUMN F Grab info----------------
    Sheets("Entry Form").Select
    Range("F9").Select '(Grab F MODEL)
    Selection.Copy
'Add to Auto List Column F
    Sheets("Entry Form").Select
    Range("F25").Select
    Selection.End(xlDown).Select 'Go to last item
        ActiveCell.Offset(1, 0).Range("A1").Select 'then one more to the next blank spot
    'Paste value with invisible formatting
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
        'Selection.NumberFormat = ";;;"
'----------------END COLUMN F Grab info-------------

'============Remove Duplicates from Column F============
Range("F25").End(xlDown).Select
ActiveSheet.Range("F25", Range("F25").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo

'Add color back to Any that had it removed
Range(Selection, Selection.End(xlUp)).Select
    'Range(Selection, Selection.End(xlUp)).Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Selection.NumberFormat = ";;;"
'============End Remove Duplicates from Column D=========
'COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F COLUMN F

'**********++++++++++============END GRABBING INFO============++++++++++**********
'Reprotect Sheet
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
Range("B7").Select
ActiveWorkbook.Save
    Application.ScreenUpdating = True
End Sub

Thank you for your response @DisplayName. I have almost no experience with activex combo boxes.
I like where your code is going though.

Your code is great, I just need it to work with tabbing.

Solution

If I'm correctly guessing what you want to do, then I'd say you call for an "on-the-fly" ActiveX ComboBox

what follows assumes that:

you don't have any ActiveX combobox in your sheet already

actually you must not have any ActiveX control or any linked or embedded OLE object in the sheet
you don't have a Worksheet_Change event handling in your sheet code pane

then you may try putting the following code in the "Entry Form" sheet code pane (explanations in comments)

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    If OLEObjects.Count > 0 Then 'check for any existing activeX combobox already in the sheet
        With OLEObjects("myDD") ' if so, then reference the combobox you must have put through this code (see below)
            If .Object.ListIndex = 0 Then ' if no elements selected in the combobox list
                Range(.LinkedCell).ClearContents ' then clear the content of the cell you linked to the combobox through this code (see below)
            Else 'otherwise
                Range(.LinkedCell).Value = .Object.Value ' fill the content of the cell linked to the combobox with this latter selected value
                ListUpdate Range(.LinkedCell) 'try and update the range from which combobox will be filled with
            End If
            .Delete ' delete the combobox and leave underneath cell visible
        End With
    End If

    If target.Count <> 1 Then Exit Sub ' if selection is not a single cell then exit
    If Intersect(target, Range("B7, D13, F9")) Is Nothing Then Exit Sub ' if selection is not one of the form entry cells then exit

    With target 'reference selected cell
        If IsEmpty(Cells(25, .Column).Value) Then Exit Sub ' if no values available fot the current entry cell then exit sub

        With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
                                        Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height) ' add and reference a new ActiveX combobox
            .Name = "myDD" 'name it as "myDD"
            .ListFillRange = Range(Cells(25, target.Column), Cells(Rows.Count, target.Column).End(xlUp)).Address ' fill its range with already available values
            .LinkedCell = target.Address ' link it to the selected cell
        End With
    End With
End Sub


Sub ListUpdate(target As Range)
    If IsEmpty(Cells(25, target.Column).Value) Then Exit Sub ' if no values available fot the current entry cell then exit sub
    With Range(Cells(25, target.Column), Cells(Rows.Count, target.Column).End(xlUp)) ' reference values already available
        If .Find(what:=target.Value, lookat:=xlWhole, LookIn:=xlValues) Is Nothing Then .Offset(.Rows.Count).Resize(1).Value = target.Value ' if new entered value not in the referenced values range already, then add it at the bottom of the list
    End With
End Sub