?
Solved

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

Posted on 2009-07-07
3
Medium Priority
?
1,110 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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 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