UserForm: jump to the position of selected result from listbox

[wali]

Hallo everyone,

I have a userform which has a textbox "txtSearchString". When I type a string in it and click Button "cmdSearch", it looks for the typed word in all worksheets and shows me the results in a Listbox "lstResults".

Now I want to select one of the results and jump to that cell (which contains the selected result from Listbox) by clicking the button "makeResultActive".

I will be thankful if someone could help me in writing code for the button "makeResultActive".

I already have two other buttons for other puposes which do their job well. Now I need code for the next button "makeResultActive".

 
Private Sub CommandButton3_Click()
'this Button copys the selected result from the resultlist of listbox "lstResults"
    Dim i As Integer
    Dim LR As Long
    
    LR = Sheets("de-ps").Range("A" & Rows.Count).End(xlUp).Row + 1
    
    With lstResults
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                Sheets("de-ps").Range("A" & LR).Value = .List(i)
                
                LR = LR + 1
            End If
        Next i
    End With
End Sub
 
 
Private Sub CommandButton4_Click()
' this Button replaces content of active Cell through selected result from lstResults

    Dim i As Integer
    Dim LR As Long
    
   
    With lstResults
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ActiveCell.Value = Me.lstResults.Value
                'Sheets("de-ps").Range("A" & LR).Value = .List(i)
                
                LR = LR + 1
            End If
        Next i
    End With
End Sub

 
Private Sub makeResultActive_Click()
'when clicked it should make cell of selected result from lstResults active/jumb there
 
 
End Sub

Thanks for each help in advance.

[tigeravatar]

wali,

Attached is an example workbook. It contains a macro that can be run with keyboard shortcut: Ctrl+Shift+F Using the macro will launch a userform that has a textbox that you type search criteria into, and then press Search. The results will populate the listbox. Clicking on an item in the listbox will cause that item's worksheet and cell to be selected.

Hopefully, you'll be able to use the example and apply what you need to your own workbook.

[tigeravatar]

wali,

From the Forum Rules:

4. Don't Private Message or email questions to moderators or other members. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Hello,
thank you very much for you help in my thread:

http://www.excelforum.com/excel-prog...ml#post2644305

I am sorry for not being clear enough with my problem description. The fact is that I already have a userform and all i need is a new button "makeResultActive"in it.

I will be very gratefull if you could find time and have a look at it once again. You can download my excel file from:

www.qamosona.de/downloads/Userform-wali.xls

Thank you very much.

I took a look at your file, and got the makeResultActive button working. Had to make a couple of changes to cmdSearch_Click():

Private Sub cmdSearch_Click()

    Dim wrk As Workbook
    Dim ws As Worksheet
    Dim wsTemp As Worksheet
    Dim varSearchString As String
    Dim varRow As Long
    Dim LR As Long
    Dim LR1 As Long
    
    If Me.txtSearchString.Text = "" Then
        MsgBox "Please enter a search term.", vbCritical
        Exit Sub
    End If

    Application.ScreenUpdating = False

    Me.lstResults.RowSource = ""
    Set wrk = ActiveWorkbook
    Set wsTemp = Sheets("tmpSearch")
    wsTemp.Range("A2:B50000").Clear
    For Each ws In wrk.Worksheets
        If ws.Name <> wsTemp.Name And ws.Name <> "ps-de" Then
            LR = ws.Range("A" & Rows.Count).End(xlUp).Row
            varSearchString = LCase(Me.txtSearchString.Text)

            For varRow = 1 To LR
            On Error Resume Next
                If InStr(LCase(ws.Cells(varRow, 1).Value), varSearchString) <> 0 Then
                    LR = wsTemp.Range("A" & Rows.Count).End(xlUp).Row
                    wsTemp.Range("A" & LR).Offset(1, 0).Value = ws.Cells(varRow, 1).Value
                    wsTemp.Range("A" & LR).Offset(1, 1).Value = ws.Name
                End If
                On Error GoTo 0
            Next varRow
        End If
    Next ws
    With wsTemp
        .Columns("A:A").EntireColumn.AutoFit
    End With

    Application.ScreenUpdating = True

    If wsTemp.Range("A2").Value = "" Then
        MsgBox "No results found.", vbInformation
    Else
        Me.lstResults.ColumnWidths = wsTemp.Columns(1).Width
        Me.lstResults.RowSource = "=OFFSET(tmpSearch!$A$2,0,0,COUNTA(tmpSearch!$A$2:$A$65000),1)"
    End If

End Sub

And here's the code for makeResultActive_Click()

Private Sub makeResultActive_Click()
    
    If Me.lstResults.ListIndex = -1 Then Exit Sub
    With Sheets(Sheets("tmpSearch").Range("B" & Me.lstResults.ListIndex + 2).Value)
        .Select
        .Cells.Find(What:=Sheets("tmpSearch").Range("A" & Me.lstResults.ListIndex + 2).Value, LookAt:=xlWhole).Select
    End With
    
End Sub

[wali]

Hi tigeravatar,

thank you very much for the modification. Unfortunately it shows me an error when i run my userform. It shows error in

 With Sheets(Sheets("tmpSearch").Range("B" & Me.lstResults.ListIndex + 2).Value)

I am attaching the excelform with you new code, so that you can try it. For example type "40" in searchform and click on "<= search" button. When the results are shown select any result and click on button "makeResultActive". An error accurs then

[tigeravatar]

wali,

You got an error because you didn't update cmdSearch_Click(). All I did was replace the existing cmdSearch_Click() code with the cmSearch_Click() code I provided in the earlier post and it started working correctly without error.

[wali]

Woow! it works. Thank you very much. Thanks a million times.

God bless you.