Combobox does not update and accepts wrong values

Question

Good, I have this code for a combobox, and I have two problems.

First: I can’t get the combobox to accept only the list values

Second: the listing has only one value

Private Sub TempCombo_Change()

Dim v As Variant, i As Long
With Me.TempCombo
    If .Value <> "" And .ListIndex = -1 Then
        v = Worksheets("Fardamento").Range("F4:F215").Value
        .Clear
        For i = LBound(v, 1) To UBound(v, 1)
            If LCase(v(i, 1)) Like "*" & LCase(.Value) & "*" Then
                .AddItem v(i, 1)
            End If
        Next i
    Else
        .List = Worksheets("Fardamento").Range("F4:F215").Value
    End If
End With
End Sub


Private Sub UserForm_Initialize()
With Me.TempCombo
    .Visible = True
    .ColumnCount = 1
    .AutoWordSelect = False
    .MatchEntry = fmMatchEntryNone
    .List = Worksheets("Fardamento").Range("F4:F215").Value
End With
End Sub


Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim v As Variant, i As Long
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler

If Target.Validation.Type = 3 Then
    If Target.Column = 7 Then
Cancel = True
Application.EnableEvents = False
str = Target.Validation.Formula1
Debug.Print Target
str = Right(str, Len(str) - 1)


With cboTemp
  .Visible = True
  .Left = Target.Left
  .Top = Target.Top
  .Width = Target.Width + 5
  .Height = Target.Height + 5
  .LinkedCell = Target.Address

End With
cboTemp.Activate
Me.TempCombo.DropDown
End If
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub


Private Sub TempCombo_LostFocus()
 With Me.TempCombo
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End Sub


Private Sub TempCombo_KeyDown(ByVal _
 KeyCode As MSForms.ReturnInteger, _
 ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
  ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
  ActiveCell.Offset(1, 0).Activate
Case Else
    'do nothing
End Select
End Sub

Answer 1

Combobox and data validation are different things. Try to use data validation, here is a very simple code to prevent improper data from being typed into the cell and create a combobox in the selected cell.

Sub Validação()

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Sheet2!$A$2:$A$999"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub