Solved: Auto update data fm one Worksheet to another Worksheet

[winxmun]

Hi there,

i am very new in excel macro but need to check how to update a cell from one worksheet to another worksheet.

example: To set macro at Worksheet A to allow user update certain fields & auto update to Worksheet B. Worksheet B will capture all records & disabled to user. how it function:

Step 1: User input the Account No. 1st & click on "Search" button. Excel to extract Customer Name (text box), Age (text box), date (text box) & Maker Name (droplist) from Worksheet B & display in Worksheet A.
Step 2: User to update the necessary.
Step 3: Click on "Update" button, excel to auto update the data into Worksheet B based on Account No.

Note: User must click on "Update" before exit the Excel file or input the new Account No.

Tks & Regards.

[lucas]

Why not use a userform to enter new data or to edit existing data. Attached is a simple example.

[winxmun]

Hi Lucas,

Tks for ur reply. i hv tried using ur method & half successful only. i hv problem in the 1st command button (existing record). cant extract the data for updating. in addition, i hv few more question:
1. how to stop user from changing other fields in the database?
2. how to preset the field format in the user form? eg. a Date format field.

attached file for your comments. tks.

[winxmun]

I hv tried Lucas & mdmackillop's method & it works! 5 more questions:
1. How to stop user from changing other fields in the Data Worksheet? i tried protect Data Worksheet but will affect the Macro.
2. How to preset the field format in the user form? eg. a Date format field.
3. How to open the excel file w/o asking question on disabled or enabled Macro?
4. In Create New Record form, after click on "Enter" button, the Data worksheet will be appearred. How to prevent this?
5. Can auto save excel file after updating?

Attached file for comments. tks.

[winxmun]

Anyone able to advice? tks. :anyone: :sad2:

[mdmackillop]

For point 3, have a look at this (http://vbaexpress.com/forum/showthread.php?t=9545). I'll get back to you on the others

[mdmackillop]

Give this a try

[winxmun]

Hi mdmackillop, all my problems are solved except for point 3 cos cant find the SelfCert file. I will try to find the file again. Tks a lot. :friends: :ole: :thumb