Data capturing user form

Hi,

PLEASE HELP ME

I built up this userform from bits and pieces of code from Message boards, Forms etc. I know the very basic on VBA but not enough to get me by.

I created a userform to aid in capturing our construction companies costs, and emailing orders to suppliers.
It works like this

There are four spreadsheets,
Materials - used to capture the costing data in a database for extraction by other workbooks
Print D - Simultaneously captures the data to an order for which is emailed to suppliers
Print M - as above (different products i.e. "Marmoran")
Data - contains the source data for the userform to function

The command buttons are use to
Capture data to all spreadsheets
Print the Print D or M spreadsheets
Email the Print D or M spreadsheets
Clear all input form all but the Materials spread sheet
Recall - used to capture the value of the materials ordered (not known at the time of ordering) next to the correct order number by
filling the correct order number in the "order no" textbox and the value in the "Amount" textbox

My problem:
1. The "Job Ref" text box's value changes based on the "Site" combobox value however, the VBA code only executes once or twice (by capturing) then gives an error
2. I would like to have order numbers generated automatically using the first three letters of the "Site Supervisors" names on the "data" spreadsheet and a numerical number thereafter

I know this is a mouthful but I really need this to work

Thank you SOOOOO much

Eddie1985

OH P.S. to execute please click the Koala bear

Hi jaslake

Thax for your reply,
I removed the errHandler and inserted the code as instructed but to no avail, it still gives the runtime error 1004 "method 'Range' of object'_worksheet' failed

I