I've two list boxes in my excel vba file named LstboxEmpName and LstBoxEmpId. As
I've employees with same names the reference I'm taking here (Primary Key) is
the Employee Id as it is an Unique one.
I've two combo boxes one for the Project name and the other for the month name.
The user will select a particular project and the month name, then the
LstBoxEmpName will be filled with the name of the employees present on that
particular project on that particular month. Selecting another project or month
then the LstBoxEmpName will get Cleared and the employees at that month will get
displayed over there. This I'm doing in the change event of the Project combobox
and month combobox thru a For Loop which will check for the project name and the
month name starting from the line 2 (where the first name starts) till the end
of the list. Each tiome I change either the project name or the month name then
the For Loop will be activated and those employees list will get displayed in
the LstBoxEmpName listbox.
I've some command buttons for changing the working status of the employees from
Working to Transferred or Resgined and other for entering the Salary and
Allowances and etc. The modal I'm doing is that, the user have to select the
project name and the month name, then select the employee name from the
LstBoxEmpName, then click any of these buttons to perform the activity. After
they changed the working status or entered the Salary details, then it should be
entered immediately into the master database which is a Hidden sheet in the same
workbook.
Now my query is that, the name in the LstBoxEmpName is selected and I can check
for the same name from the from the starting till end in the For Loop as
explained above. But the issue is that, in the same project and in the same
month there may be two employees with the same name, but with different Employee
Id. So it will be better if I search with employee id, but at the same time I
don't want to display the employee id listbox in the form which will be
invisible.
My query is that, once I select the particular employee name the same
corresponding employee id should automatically get selected in the lstbox2 and
based on that selection the listbox2 change event should get trigger and then
the details should get updated. You got my point frieds.
For Example, LstBoxEmpName contains the following.
David
Alex
Susan
Edwin
Alex (this is different Alex)
And LstBoxEmpId contains.
Id-001
Id-002
Id-003
Id-004
Id-005
So 2 Alex are their one with Id-002 and the other with Id-005. Now if I select
with EmpName then it will result in error. So I'll do it with EmpId but that
list box will be visible in the form.
For example Alex(Id-002) is of $3000 salary and Alex(Id-005) is of $8000 salary,
then if I mention some leave and deduct the salary for Alex(Id-005) then program
will check the employee name and do this for Alex(Id-002) instead of Alex-005.
This is Wrong, right. SO I've to check with EmpName.
My point is that, if I select David, then Id-001 should get automatically
selected in listbox2 (LstBoxEMpID) without even clicking it (how come it is
Invisible huh....). If I select Susan, then empid Id-003 should get select
automatically in the listbox2. Like this if I traverese between the names from
top to bottom and bottom to top then, the corresponding same empid in lstbx 2
should get selected automatically. And based on that selection and the Listbox2
(LstBoxEmpId) change event it will check for the project in that month and do
perform th requested operations. You got my point.
But Empid listbox is invisible, tell me how to make the selections in that based
on the selections from the empname listbox.
I'm expecting something like below (i may be wrong, pls pardon me)....
LstBoxEmpId.Index=LstBoxEmpName.Index
or
LstBoxEmpId,SelectedText=LstBoxEmpName.List(....)
something like this may be the formula for this but I can't assume or find it
out.
Please help me in this regard and based on the completion of this only I've
planned to take one week vacation for my home town. Please help me.
If you have any queries in this regard, please do mail me and I'll let you know
clearly....