User Form Vlookup help?

[Guest]

I have 3 sheets in my file.

1. "Data" , in this one i have the employee information as follow:
A B C
ID NAME CODE
1060051 CHRIS ADAMS 1112
1060101 JOHN SMITH 9875

2. "Menu" i have the buttons with macros to bring up the User Form that i
created previously

3. "Report" In which i have 4 colums , A=ID, B=NAME,C=CODE , D= CITY

So i have a User Form in there I have 1 comobox with the user id in which
you choose the id from a dropdown list, and then i have 3 text boxes, with
the NAME, CODE and City, and one command button which says ADD

Here is what i wanna do, as soon as i choose an ID number, i want the in
the NAME textbox and CODE textbox to bring the actual information from
"Data" worksheet from the ID I chose, and then I will just input the City
manually in Textbox City, and the push the command button ADD, and all this
information (the 4 textboxes) goes to the worksheet "Report".

This is so i can built a report easily.

Anyone can help me.

Thanks !

 

[Guest]

Carlos: usually odo this code in the main VBA module

Public Finished
sub main()
Finish = false


do while Finish = False

userform1.show



loop

end sub

I then add a command buttons to my userform to indicate when I'm finished
which I make public so it recognizes the finish variable

Sub CommandButtonDone_Click()

Finish = True
UserForm1.Hide

End Sub

You can then have the combobox hide the user form (returns to main sub) when
any actions are taken and havve the main code make the modifications to the
worksheet.

 

[Guest]

Joel,

thanks for your help, but i have trouble retrieving the data from the "Data"
worksheet, to display it in the User Form, do you have a VBA code to do this?

thanks for your help.

 

[Guest]

Carlos: I think you need to break the job into two tasks. Get the code to
work between straight VBA code and the worksheets. Then get the code to work
with the userform. Create a test subrouting that simply reads and writes the
variabbles to your worksheets. Get this code working. There are plenty of
examples of this type code at other postings. Then use tthis tested code
with you userform code.

I always try to break statements up into smaller parts to make it easier to
debvug.

for example
set myrange = sheets("sheet1").range(cells(1,1), _
cells(rows.count,1).End(xlup).row))

instead I write this code as follows
Lastrow = cells(rows.count,1).end(xlup).row
set myrange = sheets("sheet1").range(cells(1,1),cells(Lastrow,1))


There is a number of important rules that mustt be obeyed when writing code
1) You have to be able to get the code to work. Being able to debug the
code is as important as writing the code
2) You have to be able to maintain the code (make changes). Make surte you
comment tthe code well so you can changge tthe code in the future. Donn't
use tricks thatt you won't remember next week,.
3) Break statement up into small pieces and use descriptive variable names.
Instread of
for i = 1 to 10

it is better to use

for rowCount = 1 to 10.

4) Don't be a "Power Programmer". "Power Programming" is archaic. It use
to be when Programming was expensive and memory was expensive to attempt to
write very efficient code using the lestt number of instructions. Now it is
more important to save time. Complicated instructions is expensive and
shouldn't be used. Make is sweat and simple!!!