Select worksheet cell from listbox selection

[ipbr21054]

On my userform I type a value in Textbox1 & run the command button code.

The code searches for the value & populates listbox1 with the row number.

Now I can code the listbox so when I select one of the rows it then takes me to the cell in column A, this is as much as I can do.
So currently I select say 345 in the listbox & im taken to cell A345

BUT I’m looking to do it this way.


I search for VA2 & the first found value was say B57 & the second found value was Z99

So in the listbox I will see 57 & 99
Clicking on 57 will take me to A57 & clicking on 99 will take me to A99

How is it written so when I click on 57 I’m taken to the cell B57 & on 99 taken to Z99
Both of which the value VA2 were found

Thanks

 

[6StringJazzer]

The strategy is to populate the listbox with cell addresses instead of row numbers. Or, if you just want to show row numbers, you can put the cell addresses in an invisible second column. Please describe what you want the user experience to be, and show the entire Sub that has the code you are using to populate the listbox.

 

[ipbr21054]

Morning,

Here is the code that populates the Listbox & also the code of which at present the user clicks in the listbox & is taken to the row in question column A

Private Sub FindTheValue_Click()
    Application.ScreenUpdating = False
    Dim fnd As Range, srcRng As Range, i As Long, sAddr As String, srcWS As Worksheet, dic As Object
    Set srcWS = Sheets("DATABASE")
    Set srcRng = srcWS.Range("C5", srcWS.Range("K" & Rows.Count).End(xlUp))
    Set dic = CreateObject("scripting.dictionary")
    If TextBox1.Value = "" Then
        MsgBox "PLEASE TYPE A VALUE TO SEARCH FOR", vbCritical + vbOKOnly, "SEARCH BOX IS EMPTY"
    Else
        Set fnd = srcRng.Find(Me.TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)
        If Not fnd Is Nothing Then
            sAddr = fnd.Address
            Do
                If Not dic.Exists(fnd.Row) Then
                    dic.Add fnd.Row, Nothing
                    ListBox1.AddItem fnd.Row
                End If
                Set fnd = srcRng.FindNext(fnd)
            Loop While fnd.Address <> sAddr
            sAddr = ""
        Else
            MsgBox "NOTHING TO MATCH THE SEARCH VALUE", vbCritical, "VALUE NOT FOUND MESSAGE"
            TextBox1.Value = ""
            TextBox1.SetFocus
        
        End If
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub ListBox1_Click()
Range("A" & ListBox1.List(ListBox1.ListIndex, 0)).Select
End Sub

 

[ipbr21054]

@6StringJazzer did you see my reply.

Many thanks

 

[6StringJazzer]

Yes, but my availability is a bit limited. I may get time to look tomorrow. This not a quick answer.

 

[ipbr21054]

No problem at all, just asking should it have slipped by.

Thanks

 

[6StringJazzer]

You're going to add the address of the found cell, instead of the row number.

Private Sub FindTheValue_Click()
    Application.ScreenUpdating = False
    Dim fnd As Range, srcRng As Range, i As Long, sAddr As String, srcWS As Worksheet, dic As Object
    Set srcWS = Sheets("DATABASE")
    Set srcRng = srcWS.Range("C5", srcWS.Range("K" & Rows.Count).End(xlUp))
    Set dic = CreateObject("scripting.dictionary")
    If TextBox1.Value = "" Then
        MsgBox "PLEASE TYPE A VALUE TO SEARCH FOR", vbCritical + vbOKOnly, "SEARCH BOX IS EMPTY"
    Else
        Set fnd = srcRng.Find(Me.TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)
        If Not fnd Is Nothing Then
            sAddr = fnd.Address
            Do
                If Not dic.Exists(fnd.Row) Then
                    dic.Add fnd.Row, Nothing
                    ListBox1.AddItem fnd.Address
                End If
                Set fnd = srcRng.FindNext(fnd)
            Loop While fnd.Address <> sAddr
            sAddr = ""
        Else
            MsgBox "NOTHING TO MATCH THE SEARCH VALUE", vbCritical, "VALUE NOT FOUND MESSAGE"
            TextBox1.Value = ""
            TextBox1.SetFocus
       
        End If
    End If
    Application.ScreenUpdating = True
End Sub

Then for the click use that address instead of defaulting to column A:

Private Sub ListBox1_Click()
   Range(ListBox1.List(ListBox1.ListIndex, 0)).Select
End Sub

 

[ipbr21054]

Thanks I will take a look when home later.
Currently my code take me to row number.

Can the new code above also advise row number & enter listbox but when clicked it will go to the found cell not row number cell A

 

[ipbr21054]

I have trid the code but i see it adds the value to listbox in order.

Example my first found value is in row 5 but in the listbox all other found value that are found are shown firset,make sense ?

 

[6StringJazzer]

Didn't it do the same thing when you were using rows?

You would have to use a sort algorithm of some kind. That's really a new question.