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

Posted on 2009-07-07
Last Modified: 2013-11-10

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?

Question by:JulienVan

Accepted Solution

TiranDagan55 earned 500 total points
ID: 24793817
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


Author Comment

ID: 24794223
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.

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now