danovkos
01-25-2010, 08:29 AM
Hi all,
please can you help me with this code?
It search in my DB in column A based textbox value. If it find something, it return this value. But now i figured out, that if there are more values wich starting with the same value (e.g. John) then it retur after each ENTER only each second value not each, which is in DB.
e.g.
in textbox i write John and press enter
My DB contains values
John Doe
john black
john white
john purple
john lila
It find the first value "john Doe", that OK, after other enter it have to find john black, but it return "john white" (that wrong), after enter again it "return lila" (wrong again).
Please where is problem? can you help me?
thx a lot
here is code:
Private Sub TextBoxALL_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Do
'Loop
Sheets("ALL").TextBoxALL.Activate
' If Me.TextBoxALL.Value = "" Then Exit Sub
If KeyCode = 13 Then
Dim rSrch As Range
Static rCl As Range
Dim fnd As String
Dim meno As String
Static por As String
Dim FirstAddress As String
meno = Range("j1").Value
fnd = Me.TextBoxALL.Value
Set rSrch = Range(Cells(30, 1), Cells(Rows.Count, 2).End(xlUp))
If rCl Is Nothing Then
Set rCl = rSrch.Cells(1, 1)
Else
Set rCl = rCl.Offset(1, 0)
If Application.Intersect(rCl, rSrch) Is Nothing Then Set rCl = rSrch.Cells(1, 1)
End If
With rSrch
Set rCl = .Find(fnd, After:=rCl, LookIn:=xlValues, LookAt:=xlPart)
If Not rCl Is Nothing Then
FirstAddress = rCl.Address
Cells(Rows.Count, 35).End(xlUp).Offset(0, -25).Value = rCl.Value
If rCl.Value = meno Then
Set rCl = .FindNext(rCl)
Cells(Rows.Count, 35).End(xlUp).Offset(0, -25).Value = rCl.Value
End If
Else
MsgBox "Dlžník sa nenachádza v našom archíve (rok 2007 - súčasnosť) "
Set rCl = Nothing
End If
End With
With TextBoxALL
.SelStart = 0
.SelLength = 500 ''anything >= to len of text
End With
End If
If KeyCode = vbKeyTab Then
With TextBoxALL
TextBoxALL.Value = ""
Call VlozCIFdoVIEW_Click
End With
End If
If KeyCode = vbKeyControl Then
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
Set rCl = Nothing
End If
'If ZMAZvALL_Click = True Then
' TextBoxALL.Value = ""
' ActiveSheet.TextBoxALL.Activate
' Range("j1").ClearContents
' Set rCl = Nothing
'End If
End Sub
please can you help me with this code?
It search in my DB in column A based textbox value. If it find something, it return this value. But now i figured out, that if there are more values wich starting with the same value (e.g. John) then it retur after each ENTER only each second value not each, which is in DB.
e.g.
in textbox i write John and press enter
My DB contains values
John Doe
john black
john white
john purple
john lila
It find the first value "john Doe", that OK, after other enter it have to find john black, but it return "john white" (that wrong), after enter again it "return lila" (wrong again).
Please where is problem? can you help me?
thx a lot
here is code:
Private Sub TextBoxALL_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Do
'Loop
Sheets("ALL").TextBoxALL.Activate
' If Me.TextBoxALL.Value = "" Then Exit Sub
If KeyCode = 13 Then
Dim rSrch As Range
Static rCl As Range
Dim fnd As String
Dim meno As String
Static por As String
Dim FirstAddress As String
meno = Range("j1").Value
fnd = Me.TextBoxALL.Value
Set rSrch = Range(Cells(30, 1), Cells(Rows.Count, 2).End(xlUp))
If rCl Is Nothing Then
Set rCl = rSrch.Cells(1, 1)
Else
Set rCl = rCl.Offset(1, 0)
If Application.Intersect(rCl, rSrch) Is Nothing Then Set rCl = rSrch.Cells(1, 1)
End If
With rSrch
Set rCl = .Find(fnd, After:=rCl, LookIn:=xlValues, LookAt:=xlPart)
If Not rCl Is Nothing Then
FirstAddress = rCl.Address
Cells(Rows.Count, 35).End(xlUp).Offset(0, -25).Value = rCl.Value
If rCl.Value = meno Then
Set rCl = .FindNext(rCl)
Cells(Rows.Count, 35).End(xlUp).Offset(0, -25).Value = rCl.Value
End If
Else
MsgBox "Dlžník sa nenachádza v našom archíve (rok 2007 - súčasnosť) "
Set rCl = Nothing
End If
End With
With TextBoxALL
.SelStart = 0
.SelLength = 500 ''anything >= to len of text
End With
End If
If KeyCode = vbKeyTab Then
With TextBoxALL
TextBoxALL.Value = ""
Call VlozCIFdoVIEW_Click
End With
End If
If KeyCode = vbKeyControl Then
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
Set rCl = Nothing
End If
'If ZMAZvALL_Click = True Then
' TextBoxALL.Value = ""
' ActiveSheet.TextBoxALL.Activate
' Range("j1").ClearContents
' Set rCl = Nothing
'End If
End Sub