Return Cell Address Of Listbox Object

  • Greetings fellow guru's :)


    I have a listbox with many entries. I need to be able to retrieve the Cell Address for any given listbox entry. Lets say my listbox has 1 Column of data. And its data is pulled from Range("A1:A3"). Like so...


    A
    1 Dog
    2 Cat
    3 Rat



    I would like to be able to click Cat in the Listbox and have $A$2 stored as the range to be selected.


    The Range will be used in a re-population Macro for a very large workbook. Imagine a revers lookup feature and that's the idea.


    Thanks again for any insight.

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Return Cell Address Of Listbox Object


    Something like this, perhaps?

    Code
    Private Sub ListBox1_Click()
    With ListBox1
    Range("C1").Value = WorksheetFunction.Index(Range(.ListFillRange), .ListIndex + 1).Address
    End With
    End Sub

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Return Cell Address Of Listbox Object


    Not quite but thanks for helping. The Listbox is a ActiveX control on a Userform. And the Range will be Dynamic. :)

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Return Cell Address Of Listbox Object


    Then use an offset from the 'Home' cell based on the ListIndex of the SelectedItem.


    Assuming A1 is the first cell.


    Code
    Private Sub ListBox1_Click() 
    
    
        MsgBox Range("A1").Offset(Listbox1.Listindex).Address
    End Sub


    (I think... :))

  • Re: Return Cell Address Of Listbox Object


    Try this:-


    Regards Mick

  • Re: Return Cell Address Of Listbox Object


    Thanks guys for all the help...


    MickG..
    This is what I need if you can help tweak it?


    1. It does return the correct address of Uniuqe values, but goes to the very last Address of duplicates.


    2. How can I make it send the Value of Listbox2 to the Address found in Listbox1?



    Thanks a heap :)

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Return Cell Address Of Listbox Object


    Try this:-
    This is for ListBox1 and Listbox2.:- (Change Rowsource List to suit , "see top of code".
    The code is based on the List having duplicates in Listbox1, whose address relates to there Listindex

  • Re: Return Cell Address Of Listbox Object


    Thanks Micks but I am getting an Object required error at this line.


    Code
    Set nRng = Dic.Item(CStr(.Value & "," & .ListIndex))


    Any thoughts?

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Return Cell Address Of Listbox Object


    Right, Thats very nice. I may have omitted some very important details "sleep deprived".


    1. I have a Macro that populates the Listboxes with data from 7 Ranges. "This Works Great"
    2. I use a Command Button to trigger the change.
    3. I also have a Code that Automatically removes data from the listbox, so the Code would need to keep that in mind.


    Thanks again Mick..Listboxes are NOT my thing at all :)

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Return Cell Address Of Listbox Object


    If you attach the file I (With explanation of whats required), I may be able to incorporate the two codes.

  • Re: Return Cell Address Of Listbox Object


    Thanks Mick for all of your help. I was able to find a work around using a 3rd Listbox. I used a Search feature along with the.Column Property. I wasn't able to post the book or a sample from it due to it being classified. But with your code I was able to make it work. So cheers to you good sir!

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!