H
Hazel
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
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