macro for one cell that functions like an nk2 file based on a worksheet'

[Elnicko]

Is it possible?

 

[Ruddles]

Explain.

 

[Elnicko]

A cell that would autocomplete text, by searching a worksheet fill of addresses.

 

[Elnicko]

I found this peice of code.

But need to wait till monday to try it out.

I think its what im after? But i need it to apply to apply to 1000 cells in a row.

Option Explicit
Dim oRange As Range
Dim iCharCount As Integer
Dim sAuto As String
Dim sTemp As String


Private Sub TextBox1_Enter()
  Set oRange = Worksheets("Sheet1").Range("a35536").End(xlUp).Offset(1, 0)
End Sub

Private Sub MyAutoComplete(ByRef oTextbox As Control)
  oRange.Value = oTextbox.Text
  sAuto = oRange.AutoComplete(oTextbox.Text)

  If Len(sAuto) > 0 Then
    With oTextbox
      sTemp = .Text
      .Text = sAuto
      .SelStart = Len(sTemp)
      .SelLength = Len(sAuto)
    End With
  End If
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'++++++++++++++++++++++++++++++++++++
'Change this next line if you are going to hard code which cell gets the data
  Sheets("Sheet1").Range("a1").Value = Me.TextBox1.Text
'++++++++++++++++++++++++++++++++++++
  oRange.ClearContents
End Sub

Private Sub TextBox1_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode >= 48 And KeyCode <= 90 Then  'Alphanumeric only
    Application.EnableEvents = False
    MyAutoComplete Me.TextBox1
    Application.EnableEvents = True
  End If
End Sub