danovkos
10-15-2009, 04:04 AM
hi all,
pls. i have two hard questions.
1)
How to change this code, that it will display msg „searching finished to end“ when it search all data till end. After OK it will search from beginning.
2) how to define, that the last part of this code (about pressing CTRL) will cleare all, as it is now, and also will clear variable, and it will start searching from beginning? Now it after clearing continue from previous position.
thank you very much for help
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 "Not found "
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
With TextBoxALL
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
End With
End If
End Sub
pls. i have two hard questions.
1)
How to change this code, that it will display msg „searching finished to end“ when it search all data till end. After OK it will search from beginning.
2) how to define, that the last part of this code (about pressing CTRL) will cleare all, as it is now, and also will clear variable, and it will start searching from beginning? Now it after clearing continue from previous position.
thank you very much for help
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 "Not found "
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
With TextBoxALL
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
End With
End If
End Sub