Data entry form

  • Thread starter Thread starter Piotr
  • Start date Start date
P

Piotr

Hi,
I wonder if there is possibility to make list field interactive, like
when user put firs letter then form showing resluts where first letter
= "user letter" ?
Becouse I have over 800 records and it is very dificult for user to
find the one is looking for.

regards
Peter
 
Piotr
This macro will do what you want. This is how it works. When the user
puts a character, say c, in B1, the entire sheet will scroll automatically
to put the first row that starts with a "c" at the top of the screen. Note
that this is a sheet macro and must be placed in the sheet module of the
sheet you are working in. To access that module, right-click on the sheet
tab, select View Code, and paste this macro into that module. Click on the
"X" at the top right corner of the screen to return to your spreadsheet.
HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngColA As Range
Dim i As Range
Dim TheRow As Long
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address(0, 0) = "B1" Then
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
If UCase(Left(i, 1)) = UCase(Range("B1").Value) Then
TheRow = i.Row
Exit For
End If
Next i
With ActiveWindow
.ScrollRow = TheRow
.ScrollColumn = 1
End With
End If
Application.EnableEvents = False
Range("B1").ClearContents
Application.EnableEvents = True
End Sub
 
Back
Top