Populate Info and Select Combobox Item from Spreadsheet based on ListBox Selection

[Dalton325]

Hello and thanks for trying to help me with my issue. I've been looking all over the internet today and I haven't been able to find a solution I can make work.

My situation is that I have a sheet called 'Current Renters'. I have unique ids in the form of (#01,#02,...#50), names, phone numbers, and other info. I took the first two columns ("A3:B" & LastRow) and used them to populate a 2 column listbox on a form called frmRenters. This shows me which lot is rented and to whom. This is working correctly.

I created a second form called frmRenterInfo. What I want to do here is use that form to not only add new renters, but to modify the current renters. So I added an 'Add' button to my frmRenters form. If I click the 'Add" button, then it displays an empty form. There is a combobox displaying the unique ids. You would select one from the drop down list, fill in the rest of the info, then hit 'Save'. It would save it to the appropriate row in the spreadsheet.

I'm having a problem with the modify current user portion. I want to select an item from the listbox and have it open frmRenterInfo, populating the combobox, textboxes, and checkbox from the info in the spreadsheet. I'm struggling with the code here.

What I've done so far is create a global variable called 'Public exist as boolean' and set it equal to true in 'Thisworkbook'. If I click the 'Add' button, then it opens the frmRenterInfo blank. When I hit save or exit on that form, it should set 'exist' back to true. However, when I double-click an item in the listbox, I want it to fill the form with the users info.

The unique id (lets say #08) would be in the in the first column. So in that instance, it should be in column 0 and row 7, considering a listbox starts at 0 on the rows and columns. I believe what I need to do is find the value of that index, search for it in the spreadsheet and when I find it, I need to populate the boxes based on the values of that row.

I have no idea how to go about that with code. I'm also having trouble setting the combobox to the value of the index/spreadsheet cell, #08 in this instance.

I'm sorry that I can't ask this any more specifically, but I haven't done a lot with comboboxes or listboxes, and I really don't even know the right questions to ask. If you guys can help me out, I'll start to get a better idea and can narrow down what I really need to know. Thanks.

PS- A piece of code I scavenged that gives me the value that I need to search is:

Private Sub lstRenters_Click()
    Dim rng As Range
    
    Load frmRenterInfo
    
    With frmRentersInfo
        Set rng = Range("A:A").Find(lstRenters.List(lstRenters.ListIndex))
        Me.cmbLot.Value = rng

    End With
End Sub
[code]

The 'me.cmbLot.value=rng' breaks on me. It says it isn't a method or object required depending on how I tweak it.

 

[Dalton325]

I've found most of a solution. To load info from one form to another, you do something similar to this:

Load yoursecondformnamehere

     yoursecondformnamehere.textboxname.text = yourvariablefromthisform

You repeat the second line and replace the relevant info. Finish by showing the second form.