Display data in Listbox based on Combo Box selection

Hi,
I am working on a VBA application in Excel 2007 and have set up a UserForm in which I have a Combo Box and a Listbox below the Combo Box. The Combo Box contains a list of ID codes. I would like a user to be able to make a selection of an ID from the Combo Box and display all information pertaining to his/her ID number such as name,address, phone, email etc. in the Listbox below. The Listbox would pick up this information from a worksheet range. This should go into the Change event of the Combo Box.


With the code I have I am able to open form . Already populated the ID field. When I select an Item in the Comobox I am getting Subscript out of Range error on the line  `With Worksheets("Agent")`.
The form is in very raw shape and data is also sketchy as I am first interested in establishing all linkages for this code to wotk. Form opening Button is on Sheet 2.

My level in Excel-VBA is Elementary. I would request that all linkage required to obtain the objective as outlined above may please be offered in the solution.

My code is as follows. Sample file is attached please. I have to have this solution on Excel 2007 attached Visual Basic Editor.

Code on Userform1

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub ComboBox1_Change()
    
    Dim rngToSearch As Range
    Dim rngToFind As Range
    Dim valToFind As Variant
    Dim arrClearList()
     
   With Worksheets("Agent")
 
 
    valToFind = ComboBox1.Value 'Edit ComboBox1 to your ComboBox name
    
  
        Set rngToSearch = .Columns("A")
    End With
    Set rngToFind = rngToSearch.Find(What:=valToFind, _
            LookIn:=xlFormulas, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
            
    If Not rngToFind Is Nothing Then
        
        'Call ClearList(Me.ListBox1)     'Optional to clear existing list
        
        ListBox1.AddItem
        
        With ListBox1
            .List(.ListCount - 1, 0) = rngToFind.Value  'ID Col A
            .List(.ListCount - 1, 1) = rngToFind.Offset(0, 1).Value 'Agent Name Col B
            .List(.ListCount - 1, 2) = rngToFind.Offset(0, 3).Value 'Address Col C
            .List(.ListCount - 1, 3) = rngToFind.Offset(0, 4).Value 'Organization Col D
            .List(.ListCount - 1, 4) = rngToFind.Offset(0, 5).Value 'Phone Col E
            .List(.ListCount - 1, 5) = rngToFind.Offset(0, 6).Value 'Email col F
        End With

    Else
        MsgBox valToFind & " not found in worksheet."
    End If
            
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim rngToSearch As Range
Dim rngToFind As Range
Dim valToFind As Variant
Dim ws As Worksheet
Set ws = Worksheets("Agent")

valToFind = ListBox1.Value 'Edit ListBox1 to your ListBox name
With Worksheets("Agent")
    Set rngToSearch = .Columns("A")
End With
    Set rngToFind = rngToSearch.Find(What:=valToFind, _
            LookIn:=xlFormulas, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
            
    If Not rngToFind Is Nothing Then
        'The GoTo command is a one line operation to
        'Select a worksheet and then select a range
        Application.Goto rngToFind
    Else
        MsgBox valToFind & " not found in worksheet."
    End If
End Sub

'The following sub is optional to be called to clear the ListBox list if required.
Sub ClearList(ctrl As Control)
    Dim i As Long
    With ctrl
        For i = 0 To .ListCount - 1
            .RemoveItem (i)
        Next i
    End With
End Sub


Private Sub UserForm_Initialize()
Dim myArray() As String
  'Use Split function to return a zero based one dimensional array.
  myArray = Split("AL|AK|AZ|AR|CA|CO|CT|DE|DC|FL|" _
             & "GA|HI|ID|IL|IN|IA|KS|KY|LA|ME|MD|" _
             & "MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|" _
             & "NY|NC|ND|OH|OK|OR|PA|RI|SC|SD|TN|" _
             & "TX|UT|VT|VA|WA|WV|WI|WY", "|")
  'Use .List method to populate listbox.
  ComboBox1.List = myArray
lbl_Exit:
  Exit Sub
End Sub

Open in new window

Code in Module1


Sub RoundedRectangle1_Click()
UserForm1.Show
End Sub

Open in new window

sample_2409b.xlsm
Sunil KakkarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Are the IDs unique?

Why use a ListBox if they are unique?
Sunil KakkarAuthor Commented:
Hi Roy_Cox
IDs are Unique. But My friend wants it to this way thatswhy I tried this approach. One more point which I missed in my earlier post is that after opening the form if I close it , there are no problems But if I choose any value in combobox then it shows the error and hangs. I have to quit excel with help of task bar.
Thanks.
Sunil
Roy CoxGroup Finance ManagerCommented:
Is this close to what you want?
sample_2409b.xlsm
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Sunil KakkarAuthor Commented:
Comment by Roy_Cox
Roy CoxGroup Finance ManagerCommented:
I don't understand your last post
Sunil KakkarAuthor Commented:
Hi Roy_Cox

Display data in Listbox based on Combo Box selection

It is giving Run-time error '381' :

Could not set the List property. Invalid prpertyarray Index.

And does not proceed further.

Comment: Array was used by me to populate the combobox. It may be preferable to load it from Sheet range.
Roy CoxGroup Finance ManagerCommented:
Have you tried the example that I posted?

If you are only displaying the data selected then use a ListBox. If you want to edit that data then it must be loaded into TextBoxes
Sunil KakkarAuthor Commented:
I Have run the file sent by you and when I run the form I am getting the error message.

 Run-time error '381' :

Could not set the List property. Invalid property array  Index.

And does not proceed further.

Since it related to array index I felt that I might have chosen wrong approach so you may consider whether populating it from worksheet range Coulmn A may be a better approach.
Roy CoxGroup Finance ManagerCommented:
It works perfectly for me. I have removed all the code in the original, have you restored it?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sunil KakkarAuthor Commented:
since this is an address directory, It may have to edited to keep it updated. In that case loading in a text box may be better approach. If I am understanding you correctly then combobox to select the ID and text box for related details instead of ListBox.
Sunil KakkarAuthor Commented:
I have noticed the changes by you. I am going to sheet 2 and clicking on the Blue rectangle gives me the following error

Run-time error '381' :

Could not set the List property. Invalid property array  Index.

I click the end button instead of Debug button to end the process. If I click the debug button then it shows error on the line
Userform1.Show

Open in new window


Sub RoundedRectangle1_Click()
UserForm1.Show
End Sub

Open in new window

sample_2409bs1.xlsm
Sunil KakkarAuthor Commented:
Hi Roy_Cox,

I made a new Userform with your code only after removing all the commented portion and it works like a charm.
Thanks for your so simple approach.

I am attaching the revised file for your perusal. Your solution is acceptable to me.
Warm Regards

Sunil
sample_2409c.xlsm
Sunil KakkarAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for skkakkar's comment #a40998084

for the following reason:

He has excellent knowledge base . His approach is for most simple solution. He is a cooperative person.
Sunil KakkarAuthor Commented:
His knowledge base is excellent. His approach is for simple solutions. He is Cooperative Person.
Sunil KakkarAuthor Commented:
Hi Roy_Cox
I regret that I made my post at wrong place earlier that is on my comments and not on Roy_Cox Solution.
Warm regards
Sunil
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.