Listbox-2 entires should select by Clicking Listbox-1 entires. How

[Sriram]

Dear Friends & Gurus,

I've 2 listboxes in my excel form one contains Employee Names and the other
Employee Id. This is for monitoring the manpower details on each month. There
may be case that 2 employees can have the same name, but their Employee Id
will be Unique. The user have a combo box where he can select the month
details, once selected then Listbox1 will be filled with the Employee Names
worked on that particualr month. If the HR Exec. wants to enter the details
of the Salary and the Allowances for the employees then he/she can select the
month name and then select the employee name from the listbox1 and click the
appropriate command buttons provided one for salary, another for allowances,
and etc.

For example the first 2 employees are having the same name, but with
different profile, salary and employee id. So when the user selects the first
employee name then a For....Loop will get activated and check for the
particular employee in that particular month. If the user selects the 2nd
employee with the same name then the loop will display the first employee
details. This I wish to achieve by checking the Employee Id in the
For....Loop, but I don't want that listbox2 contains the Employee Id to be
visible.

When the user selects or changes the month from the combo box then a
For....Loop will be activated and listbox1(Emp Name) and listbox2(Emp Id)
will get populated with employee names and employee ids respectively for that
particular selected month. Here listbox2(Emp Id) will be invisible to the
user always. Once he selects the employee name then the corresponding
selection should take place in listbox2 also.

If thes user selects the first employee name then in the listbox2 the first
entry should get selected automatically. As such if he selects the fourth
entry in list1 then 4th entry in list2 should get selected automatically and
based on that selection in the For....Loop it should check for the employee
id and details should get displayed. Belo is my code for that, but it is
giving some

Run-time error '-2417417848 (80010108): Method 'ListIndex' of object
'IMDcList' failed.

It is working for the first entry of the listbox1 and then all other entries
are giving this error.

Kindly suggest me what I should do to come out of this problem.

Here is the Code for that:
-----------------------------

Private Sub LstEmployeeNames_click()

ThisWorkbook.Sheets("Worksheet").Cells(12, 7) = "" 'Working Status

If LstEmployeeNames.Value <> "" Then
LstRecordindex.ListIndex = LstEmployeeNames.ListIndex
'MsgBox LstRecordindex.Value

For I = 4 To LastLine - 1
If ThisWorkbook.Sheets("MP Data").Cells(I, 1) =
Val(LstRecordindex.Value) Then
ThisWorkbook.Sheets("Worksheet").Cells(12, 7) =
ThisWorkbook.Sheets("MP Data").Cells(I, 5)
ThisWorkbook.Sheets("Worksheet").Cells(16, 7) =
ThisWorkbook.Sheets("MP Data").Cells(I, 15)
ThisWorkbook.Sheets("Worksheet").Cells(20, 7) =
ThisWorkbook.Sheets("MP Data").Cells(I, 8)
ThisWorkbook.Sheets("Worksheet").Cells(21, 7) =
ThisWorkbook.Sheets("MP Data").Cells(I, 9)

Exit For
End If

Next I

End If

End Sub


Thanks in advance.

Sriram

 

[Keith74]

Hi

Why not just have 1 listbox with 2 columns. You can set the width of
the ID column to be zero and thus hidden. Would make life alot easier.

hth

Keith

 

[Sriram]

Keith,

Thanks for your reply. I found out the probblem resolved the issue. Now it
is workign fine. The same solution one of my other friend (Excel Guru) told
me yesterday. Your both solution is the same and let me try to adopt this
once if I'm in need of that.

Thanks for your suggestion.