Thread: Combobox - Click row - Jump to sheet

Hello,

First of all I want to thank all of you that answer and resolve our problems, sometimes I'm really stuck with a problem and often I find a topic with my problem and the right answer. It's awesome...but I have another problem:

I have a Combobox that is used to search a product database with 4 columns (Reference, Description, Quantity and Location) and I want to be able to double click a result (a row) and jump to the worksheet that has a layout of the Location.

I have several Racks (A, B, C...) with the products, and every rack's layout is on a different worksheet with the name A, B, C, D, E, etc... And the location of the products can be B12, A102, C2 (for example, attention this is not a cell reference, it's the product's fisical location reference).

OPTIONAL:
It would be great if the cell with the location reference, in the correspondent layout worksheet, was selected (so it was quicker to see where the location of the product is).

My code (part of it):

Private Sub CommandButton1_Click()
 'SEARCH
 
 Dim Cnt As Long
 Dim Col As Variant
 Dim FirstAddx As String
 Dim FoundMatch As Range
 Dim LastRow As Long
 Dim r As Long
 Dim StartRow As Long
 Dim Wks As Worksheet
 
     StartRow = 2
     Set Wks = Sheets(1)
     
'bug fix
With ListView1
    .Sorted = False
End With
       
       
       Col = ComboBox1.ListIndex + 1
         If Col = 0 Then
            MsgBox "Escolha uma categoria."
            Exit Sub
         End If
        
       If TextBox1.Text = "" Then
          MsgBox "Introduza um termo de pesquisa."
          TextBox1.SetFocus
          Exit Sub
       End If
       
         LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
         LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
         
         Set Rng = Wks.Range(Wks.Cells(2, Col), Wks.Cells(LastRow, Col))
         
           If CheckBox1 = True Then
         
                Set FoundMatch = Rng.Find(What:=TextBox1.Text, _
                                     After:=Rng.Cells(1, 1), _
                                     LookAt:=xlWhole, _
                                     LookIn:=xlValues, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlNext, _
                                     MatchCase:=False)
            Else
                
                Set FoundMatch = Rng.Find(What:=TextBox1.Text, _
                                     After:=Rng.Cells(1, 1), _
                                     LookAt:=xlPart, _
                                     LookIn:=xlValues, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlNext, _
                                     MatchCase:=False)
            End If
                      
          If Not FoundMatch Is Nothing Then
             FirstAddx = FoundMatch.Address
             ListView1.ListItems.Clear
             
             Do
               Cnt = Cnt + 1
               r = FoundMatch.Row
               ListView1.ListItems.Add Index:=Cnt, Text:=r
                 For Col = 1 To 4
                   Set c = Wks.Cells(r, Col)
                   ListView1.ListItems(Cnt).ListSubItems.Add Index:=Col, Text:=c.Text
                Next Col
               Set FoundMatch = Rng.FindNext(FoundMatch)
             Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
             SearchRecords = Cnt
          Else
             ListView1.ListItems.Clear
             SearchRecords = 0
             MsgBox "Nenhum resultado encontrado para " & TextBox1.Text
          End If
       
End Sub



Private Sub UserForm_Activate()

  Dim c As Long
  Dim I As Long
  Dim r As Long
  Dim Wks As Worksheet
  
    With ListView1
      .Gridlines = True
      .View = lvwReport
      .HideSelection = False
      .FullRowSelect = True
      .HotTracking = True
      .HoverSelection = False
      .ColumnHeaders.Add Text:="Linha", Width:=0
    End With
    
    Set Wks = Sheets(1)
    
      For c = 1 To 4
        If Wks.Cells(1, c).Text = "Descrição" Then
            ListView1.ColumnHeaders.Add Text:=Wks.Cells(1, c).Text, Width:=200
            ComboBox1.AddItem Wks.Cells(1, c).Text
        Else
            ListView1.ColumnHeaders.Add Text:=Wks.Cells(1, c).Text
            ComboBox1.AddItem Wks.Cells(1, c).Text
        End If
      Next c
    
End Sub

Thank you in advance...

Any thoughts...???