Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Vlookup help?
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Vlookup help?
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. "Carlos" wrote: 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 ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Vlookup help?
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. "Joel" wrote: 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. "Carlos" wrote: 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 ! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Vlookup help?
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!!! "Carlos" wrote: 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. "Joel" wrote: 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. "Carlos" wrote: 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 ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I fill a cell in a user form from a selection on same form? | Excel Discussion (Misc queries) | |||
User form ComboBox Items: Remember user entries? | Excel Programming | |||
Excel VB User Form Using Vlookup? | Excel Programming | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming |