listbox selection to load to new form

[gwoodby]

Any help would be Greatly Appreciated!!!!
userForm1 Has a listbox with a list of names, When the name is
Selected it needs to search 3 sheets, then Make The Sheet That The
name is on to be Active, and load the corresponding from specified
cells how would this is what i have come up with so far but its not
working :|

Private Function StrFind(StrList As String)
Dim SearchTxt As String
Dim rng As Range
Dim sh As Worksheet

With ThisWorkbook.ActiveSheet
If ListBox.Text = "" Then
GoTo ErrorHandler
End If

TextBox1.Text = LstBox.Text


SearchTxt = FrmSelection.LboxSelect.Text
Set sh = ActiveSheet
Set rng = sh.Cells.Find(What:=SearchTxt, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
sh.Activate
' my code here
end if
End with

ErrorHandler:
MsgBox "Please Select One", vbOKOnly, "Error"
End Function

Seeing as how I am a novice Any Input would be Appreciated, and Any
Pointers as well.
thank you!

 

[dan dungan]

Hi,

I'm wondering how you know it's not working.

Which line of code produces what error?

Dan

 

[gwoodby]

Hi,

I'm wondering how you know it's not working.

Which line of code produces what error?

Dan
Private Function StrFind(StrList As String)

Dim SearchTxt As String
Dim rng As Range
Dim sh As Worksheet


With ThisWorkbook.ActiveSheet
If ListBox.Text = "" Then
GoTo ErrorHandler
End If


TextBox1.Text = LstBox.Text


SearchTxt = FrmSelection.LboxSelect.Text
Set sh = ActiveSheet
Set rng = sh.Cells.Find(What:=SearchTxt, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
sh.Activate
' my code here
end if
End with


ErrorHandler:
MsgBox "Please Select One", vbOKOnly, "Error"
End Function

When you Click the Search Button ( this code) It will Search all 3
pages, However If you select a name thats not on Page 1 then It sends
you the error Message, instead of doing what i want it to do. Wich
would be make the sheet that its on active, Then Pull the data from
the cells to the right of it. I have the Code to Pull the data, the
issue was it wont activate the sheet any ideas?

 

[dan dungan]

When you Click the Search Button ( this code) It will Search all 3
pages,

How do you know it searched all three pages? I don't know how your
data is organized on each sheet, but I don't see in your code how you
are searching all three worksheets.



However If you select a name thats not on Page 1 then It sends

you the error Message,

Which error message is it sending?

 

[gwoodby]

Private Sub CmdSearch_Click()
Dim sh As Worksheet
Dim rng As Range, firstAddress As String
Dim SearchTxt As String
SearchTxt = TxtCaseName.Text

If TxtCaseName.Text = "" Then
GoTo ErrorHandler
End If

Set sh = Worksheets("Shelter")
Set rng = sh.Cells.Find(What:=SearchTxt,
After:=sh.Cells.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rng Is Nothing Then
firstAddress = rng.Address
Do
Set rng = sh.Cells.FindNext(rng)
FrmSelection.LboxSelect.AddItem (rng) & " " & sh.Name
Loop While Not rng Is Nothing And rng.Address <> firstAddress
End If

Set sh = Worksheets("Dependency")
Set rng = sh.Cells.Find(What:=SearchTxt,
After:=sh.Cells.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rng Is Nothing Then
firstAddress = rng.Address
Do
Set rng = sh.Cells.FindNext(rng)
FrmSelection.LboxSelect.AddItem (rng) & " " & sh.Name
Loop While Not rng Is Nothing And rng.Address <> firstAddress
End If

If TxtCaseName.Text = "" Then
GoTo ErrorHandler
End If

Set sh = Worksheets("TPR")
Set rng = sh.Cells.Find(What:=SearchTxt,
After:=sh.Cells.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rng Is Nothing Then
firstAddress = rng.Address
Do
Set rng = sh.Cells.FindNext(rng)
FrmSelection.LboxSelect.AddItem (rng) & " " & sh.Name
Loop While Not rng Is Nothing And rng.Address <> firstAddress
End If
If FrmSelection.LboxSelect.ListCount >= 1 Then
Unload Me
FrmSelection.Show
Else
Unload Me
FCreate.Show
End If
ErrorHandler:
MsgBox "Please Type a Name", vbInformation, " No Names found"
End Sub

Is the SearchCode From FSearch ( userForm1)
It Pulls up Another Form ( FormSelect)
Wich has LboxSelect( ListBox)


Private Sub CmdSelect_Click()
Dim r As Range
Dim ws As Worksheet
Dim SearchTxt As String
Dim Row As Integer

If Me.LboxSelect.Text = "" Then
GoTo ErrorHandler
Else
Search (LboxSelect.Text)
End If
ErrorHandler:
MsgBox "Please Select One", vbOKOnly, "Error"
End Sub


Is The Selection Button It calls My Search Function

Private Function Search(strToSearch As String)
Dim sh As Worksheet
Dim rng As Range, firstAddress As String
Dim SearchTxt As String
SearchTxt = strToSearch
For Each sh In ThisWorkbook.Worksheets

Set rng = sh.Cells.Find(What:=SearchTxt, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)

If Not rng Is Nothing Then
GoTo ErrorHandler
Else
Found
End If
Next sh
ErrorHandler:
MsgBox "Error" & strToSearch & "Not Found"

End Function and here is where my Issues begin
When It calls The Function to Do What i need it to do It wont Activate
the Sheet

Private Function Found(StrList As String)
Dim SearchTxt As String
Dim rng As Range
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
SearchTxt = FrmSelection.LboxSelect.Text
If FrmSelection.LboxSelect.Text = "" Then
GoTo ErrorHandler2
Else
Set rng = sh.Cells.Find(What:=SearchTxt, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)


If Not rng Is Nothing Then
sh.Activate
' My Code to Enable or Disable Textboxes and to Fill TextBoxes goes
Here
Else
GoTo ErrorHandler2
End If
End If
Next sh
End Sub

I am doing this right correct? I mean, This is my First Time ever
using Functions and Im still Learning so i really dont know :|