selecting listbox item takes you to that item

[rickblunt]

Hello, is it possible to click (or maybe double click would be better?) on an item in a listbox, and then have the worksheet row on the where that value is located be shown? Like a hyperlink to that information.

I tried using the macro recorder to see if I could get anything, but I did not get any actions recorded when i selected the listbox item. I figure ranging it as a variable to get to the worksheet should be straight forward enough, but I can't figure out how to capture clicking on the value in the listbox. The listbox is on a worksheet and not in a userform, does thast make a difference? Selecting it is easy enough, maybe I need to add a commandbutton and use that after you select the item?

I appreciate any input at all - thanks,

 

[DanteAmor]

You can put here what instruction you used to load the data into the listbox.
The data in the sheet is filtered?

 

[rickblunt]

Thanks for responding Dante - Ahhh.... I think I see why you are starting off with this. - Here is the code that I am using to fill the listbox and yes, the sheet is filtered.

I was kind of thinking that I would be needing to reference this so that I can use this data to target my search. (One of the list box columns is a device name (column 2) and another one (column 3) is a failure date.) I will have the device names repeat over time as the same device gets repaired, but the failure dates should always be unique.

Private Sub RepairHistoryButton_Click()

 Dim sh As Object, sh2 As Worksheet
  Dim i As Long
  
  Set sh = Sheets("Dashboard")
  Set sh2 = Sheets("Repair Log")
    
  sh.RepairHistory.Clear
  For i = 1 To sh2.Range("A" & Rows.Count).End(3).Row
    If sh2.Range("A" & i).Value = sh.RepairedDevice.Value Then
      With sh.RepairHistory
        .AddItem
        .List(.ListCount - 1, 0) = sh2.Cells(i, 2).Value
        .List(.ListCount - 1, 1) = sh2.Cells(i, 3).Value
        .List(.ListCount - 1, 2) = sh2.Cells(i, 4).Value
        .List(.ListCount - 1, 3) = sh2.Cells(i, 5).Value
        .List(.ListCount - 1, 4) = sh2.Cells(i, 6).Value
       End With
    End If
  Next

End Sub

 

[DanteAmor]

I added this line to the listbox load:
.List(.ListCount - 1, 5) = i

That way we will know which line you are selecting with double click.

Try this:

Private Sub RepairHistory_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim sh As Object, sh2 As Worksheet
  Dim i As Long
  
  Set sh = Sheets("Dashboard")
  Set sh2 = Sheets("Repair Log")
  With sh.RepairHistory
    i = .List(.ListIndex, 5)
    sh2.Select
    sh2.Range("A" & i).Select
  End With
End Sub

Private Sub RepairHistoryButton_Click()
  Dim sh As Object, sh2 As Worksheet
  Dim i As Long
  
  Set sh = Sheets("Dashboard")
  Set sh2 = Sheets("Repair Log")
    
  sh.RepairHistory.Clear
  For i = 1 To sh2.Range("A" & Rows.Count).End(3).Row
    If sh2.Range("A" & i).Value = sh.RepairedDevice.Value Then
      With sh.RepairHistory
        .AddItem
        .List(.ListCount - 1, 0) = sh2.Cells(i, 2).Value
        .List(.ListCount - 1, 1) = sh2.Cells(i, 3).Value
        .List(.ListCount - 1, 2) = sh2.Cells(i, 4).Value
        .List(.ListCount - 1, 3) = sh2.Cells(i, 5).Value
        .List(.ListCount - 1, 4) = sh2.Cells(i, 6).Value
        .List(.ListCount - 1, 5) = i
       End With
    End If
  Next
End Sub

 

[rickblunt]

I added this line to the listbox load:
.List(.ListCount - 1, 5) = i

That way we will know which line you are selecting with double click.

Try this:

Private Sub RepairHistory_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim sh As Object, sh2 As Worksheet
  Dim i As Long
 
  Set sh = Sheets("Dashboard")
  Set sh2 = Sheets("Repair Log")
  With sh.RepairHistory
    i = .List(.ListIndex, 5)
    sh2.Select
    sh2.Range("A" & i).Select
  End With
End Sub

Private Sub RepairHistoryButton_Click()
  Dim sh As Object, sh2 As Worksheet
  Dim i As Long
 
  Set sh = Sheets("Dashboard")
  Set sh2 = Sheets("Repair Log")
   
  sh.RepairHistory.Clear
  For i = 1 To sh2.Range("A" & Rows.Count).End(3).Row
    If sh2.Range("A" & i).Value = sh.RepairedDevice.Value Then
      With sh.RepairHistory
        .AddItem
        .List(.ListCount - 1, 0) = sh2.Cells(i, 2).Value
        .List(.ListCount - 1, 1) = sh2.Cells(i, 3).Value
        .List(.ListCount - 1, 2) = sh2.Cells(i, 4).Value
        .List(.ListCount - 1, 3) = sh2.Cells(i, 5).Value
        .List(.ListCount - 1, 4) = sh2.Cells(i, 6).Value
        .List(.ListCount - 1, 5) = i
       End With
    End If
  Next
End Sub

Thanks Dante, I will try it out tonight and let you know. I think I am understanding what you are saying in the code, it will take me a bit to understand it though I really appreciate the knowledge and the guidance.

 

[rickblunt]

Hey Dante, that worked great! I am getting a null error when I call a device that is on the list more than once, but solo ones come up just fine - I am sure that once I put in the second qualifier this will take care of that. Thanks again for teaching me more about coding and pointing me in a different direction.

 

[rickblunt]

Hey Dante, that worked great! I am getting a null error when I call a device that is on the list more than once, but solo ones come up just fine - I am sure that once I put in the second qualifier this will take care of that. Thanks again for teaching me more about coding and pointing me in a different direction.

Hey Dante - never mind on that null error comment (wish I could simply edit my earlier post to delete it) your code was PERFECT!- made a noob mistake when I was typing your code in.

 

[DanteAmor]

Hey Dante - never mind on that null error comment (wish I could simply edit my earlier post to delete it) your code was PERFECT!- made a noob mistake when I was typing your code in.

I like to hear that.
I'm glad to help you. Thanks for the feedback.