Find ID Number
Hi Bob
At the moment we already have a print Button on the Userform with the usual
code
Private Sub CommandButton1_Click()
UserForm1.PrintForm
End Sub
Of course the smart a***s looking over my shoulder think it should be easy
to find out of 500 or so records the agents other records and on which other
sheet they should be on -- they must think I have a memory like an elephant
is supposed to have. Hope this helps
--
Many thanks
hazel
"Bob Phillips" wrote:
I thought it would be something like that. So after the first is found, and
shown on the form, how do you want to continue to the next?
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Hazel" wrote in message
...
Hi Bob
As each sheet relates to a different area that the agent covers,more than
likely after viewing, print the data in the userform boxes for the our
records and send the agent at the end of each month a hard copy for each
area.
--
Many thanks
hazel
"Bob Phillips" wrote:
What do you want to do with each as you find them?
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"Hazel" wrote in message
...
Hi Everybody
I found the code below on this forum and adapted it to my userfrom and
it
works great no problems. However with a 26 sheet workbook it always
finds
the
last ID number in Column A. For example if ID number WM3898 is on
Sheets
5,11,23 it will always show in the useform the info on sheet 23. Is it
possible to add a CommandButton that would find the first ID number in
the
workbook then on click would find the same ID number if it is within
the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.
Private Sub cmdfind_Click()
Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet
FindWhat = TextBox21.Text
Matches = False
For Each sht In Sheets
If TypeName(sht) = "Worksheet" Then
Set rngToSearch = sht.Columns("A")
Set rngFound = rngToSearch.Find(What:=FindWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not (rngFound Is Nothing) Then
Matches = True
sht.Select
rngFound.Select
End If
End If
Next
If Matches = False Then
MsgBox "Sorry " & TextBox21 & " was not found."
Else
Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value
TextBox21.Value = ""
End If
Application.ScreenUpdating = True
End Sub
--
Many thanks
hazel
|