pir81
08-31-2011, 05:59 AM
Dear all,
I have two worksheets. One is the input, the other holds a list of 100s of names (ultimatley it will be two separate work books because the input workbook will be started from zero each year, but the list sheet will grow... but step after step.. as I assume that debuging is easier within the same workbook first and I don't have the skills and understanding to skip this step with confidence).
I know that I could copy/link all the names into the cells above my input cells and hide them, but I don't think that is a very clean way.
Googeling a solution I found this
http://www.mrexcel.com/forum/showthread.php?t=31107
for a text field and tried to adapt it to a cell. But for some reason I am not able to get it to run.
Can you please help me understand where I am wrong?
Option Explicit
Dim oRange As Range
Dim iCharCount As Integer
Dim strAuto As String
Dim sTemp As String
Private Sub Worksheet_Change(ByVal Target As Range)
' to which cell changes to react (this works fine)
If Not Intersect(Target, Range("B2:C10000")) Is Nothing Then ' if intersect is nothing it would be out of range
If Range("B10000").End(xlUp).Row = Range("C10000").End(xlUp).Row Then
Range("F1").Formula = Range("B10000").End(xlUp).Row
End If
' here starts the autocomplete
Set oRange = Worksheets("list").Range("a2").End(xlUp).Offset(999, 0) 'select from A2 to A1000
oRange.Value = Target.Text
strAuto = oRange.AutoComplete(Target.Text)
If Len(strAuto) > 0 Then
With Target
sTemp = .Text
.Text = strAuto
.SelStart = Len(sTemp)
.SelLength = Len(strAuto)
End With
End If
MsgBox Target.Text
oRange.ClearContents
End If
End Sub
Any help is very much appreciated.
I have two worksheets. One is the input, the other holds a list of 100s of names (ultimatley it will be two separate work books because the input workbook will be started from zero each year, but the list sheet will grow... but step after step.. as I assume that debuging is easier within the same workbook first and I don't have the skills and understanding to skip this step with confidence).
I know that I could copy/link all the names into the cells above my input cells and hide them, but I don't think that is a very clean way.
Googeling a solution I found this
http://www.mrexcel.com/forum/showthread.php?t=31107
for a text field and tried to adapt it to a cell. But for some reason I am not able to get it to run.
Can you please help me understand where I am wrong?
Option Explicit
Dim oRange As Range
Dim iCharCount As Integer
Dim strAuto As String
Dim sTemp As String
Private Sub Worksheet_Change(ByVal Target As Range)
' to which cell changes to react (this works fine)
If Not Intersect(Target, Range("B2:C10000")) Is Nothing Then ' if intersect is nothing it would be out of range
If Range("B10000").End(xlUp).Row = Range("C10000").End(xlUp).Row Then
Range("F1").Formula = Range("B10000").End(xlUp).Row
End If
' here starts the autocomplete
Set oRange = Worksheets("list").Range("a2").End(xlUp).Offset(999, 0) 'select from A2 to A1000
oRange.Value = Target.Text
strAuto = oRange.AutoComplete(Target.Text)
If Len(strAuto) > 0 Then
With Target
sTemp = .Text
.Text = strAuto
.SelStart = Len(sTemp)
.SelLength = Len(strAuto)
End With
End If
MsgBox Target.Text
oRange.ClearContents
End If
End Sub
Any help is very much appreciated.