Solved

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

Posted on 2009-07-07
3
1,091 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

724 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