Textbox autocompletion using values from a column of an Excel sheet with VSTO


I'm developing an Excel add-in, and I'd like to parameterize the autocompletesource of a Textbox to use values from the column of a worksheet.

The problem is that the Excel file is always opened by the user and so I cannot use it as a data source with a standard connection.

I'd like to not reload all the values on each display of the textbox because it can become very long. Is there a way to use an opened Excel spreadsheet as a data source?

Or would it be possible to bind a DataTable on an opened Excel spreadsheet , and update it by detecting any modification in the sheet?

Who is Participating?
TiranDagan55Connect With a Mentor Commented:
Take a look at this external link with a solution:

Option Explicit
Dim oRange As Range
Dim iCharCount As Integer
Dim sAuto As String
Dim sTemp As String
Private Sub EnteredName_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 EnteredName_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.EnteredName.Text
End Sub
Private Sub EnteredName_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode >= 48 And KeyCode <= 90 Then  'Alphanumeric only
    Application.EnableEvents = False
    MyAutoComplete Me.EnteredName
    Application.EnableEvents = True
  End If
End Sub

Open in new window

JulienVanAuthor Commented:
Thanks, I didn't think about the autocompletion of Excel, it is a very good idea.
I'll try this solution and come back after to tell you if it's working in my case.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.