Search option with a few required outcomes

[unreal_event_horizon]

Hi there,
have searched and searched with no clear explanation of how a search code would work so i have been studying up but i cant see where to start.

I have a spreadsheet, in this spread sheet are staff and personnel numbers. however there are 3 columns of staff names and 3 columns of personnel numbers, above the names of the staff in seperate columns are contract, permanent and temporary which these staff all fall under.

to display it i guess it would look like this

TEMP/ /PERM/ /CONTRACT/
name /number /name /number /name number/


what i want to do is enter the personnel number which will inturn display the

status ie temp,contract,perm and the staff name.

i know there is an easy way because i was going to simply start entering line and line of the following code to match each each cell which defeats the purpose.

Please Login or Register  to view this content.

[stojko89]

Can you upload a sample workbook?

[unreal_event_horizon]

excelforum.xls

edit now attached

[stojko89]

So If I understand you want to enter a number in Cell F7 and the results of Temp, Contract, Permanent and staff name would be displayed where?
Should the values be copyed to a specific range or should the cells just be selected?
Also numbers from which column will you write in the F7 cell?
Numbers from column D, G ,J or M?

[unreal_event_horizon]

sorry stojko, I have no userform on that workbook, but i can set one up, but what i want it to do is enter in the number in a textbox in the userform, then hit submit and have a new userform display with the staff name, contract and re-display the number i just entered, as i said i was going to enter if statements with corresponding staff numbers but every time i have a new staff member the new number would need to be entered into the code.

[stojko89]

You can have 1 userform for all that.
Addiing a member, viewing the info and editing or calling the info with the textbox_AfterUpdate code.

[unreal_event_horizon]

that would be great, what vba code would i use to call up these refences,

for example if i enter staff number 244001 i want staff name and contract to appear.

i could just have

Please Login or Register  to view this content.

but this is horribly time consuming and i would rather have a simple bit of code that searches the page and show a certain two cells if a staff number is entered.

thanks for your input so far

[stojko89]

Here is your workbook back and check what I did.
In the first text box enter any code from Column D
And hit TAB or an arrow it will populat other text boxes if the code is found in column D and an Edit button will show
If it's not found then an Add button will be shown for adding a new member.

Hit the Call UserForm button to call the userform and start to work with it.
Anything you don't want you can check in the code to remove.
Eather way get back to me and let me know if this is something you wanted or not

[unreal_event_horizon]

Thanks Stojko89,
had a look at your file, seems to work but not for what i want,

here is another attached file with a user form and what i would like it to do,

excelforumV2.xls

here is the code. you will see the labour involved in the code with the staff numbers inserted in the code for it to return the corresponding cells.

Please Login or Register  to view this content.

i want to be able to do this by using VBA to search a column for a number and show two other cells with the contents of the initial cell all in a tidy user form

thanks for your input and i am open to any advice, i also have removed a lot of my data to show a small working example.

[stojko89]

Ok workbook back

[stojko89]

I just hope that your column M will always be the longest because of this part of the code:

Please Login or Register  to view this content.

If it won't be the longest column then change this part of the code ^^ to this only:

Please Login or Register  to view this content.

^^ This will search the range from D10 to M10000

[unreal_event_horizon]

Many thanks Stojko89,

now to implement it to the real file

regards
Dave

[unreal_event_horizon]

Many thanks Stojko89,

now to implement it to the real file

regards
Dave