Solved

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

Posted on 2009-07-07
3
1,073 Views
Last Modified: 2013-11-10
Hi,

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?

Thanks,
Julien
0
Comment
Question by:JulienVan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 3

Accepted Solution

by:
TiranDagan55 earned 500 total points
ID: 24793817
Take a look at this external link with a solution:
http://www.mrexcel.com/forum/showthread.php?t=31107

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
'++++++++++++++++++++++++++++++++++++
  oRange.ClearContents
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

0
 
LVL 2

Author Comment

by:JulienVan
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.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel compare strings 6 52
Excel shared spreadsheet 12 37
How do you think this website does searches? 5 34
Resolving #VALUE error in spreadsheet 2 17
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 article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

737 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