Data capturing user form

[Eddie1985]

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

[jaslake]

Hi Eddie1985
This, I'm sure, will not fix everything but I see one thing that's happening and causing MsgBox "DAMIT!!!!". This code is being triggered (inappropriately) when you click the Clear Button. Add the line of code as indicted.

'THIS CODE ONLY RUNS A FEW TIMES THEN GIVES AN ERROR'

Private Sub cboJobRef_Change()
If Flag = True Then Exit Sub  '<------------ Add this line of Code
On Error GoTo errHandler:
    Dim Rw  As Long
    With Me
   
        Rw = Me.cboJobRef.ListIndex + 3
        .txtSite.Value = ws.Cells(Rw, 1).Value
        End With
        Me.cboJobRef.List = ws.Range("JobNamDyn").Value
    Exit Sub
errHandler:
         MsgBox "DAMIT!!!!"
    End Sub

Then in your Clear Button Code (at the bottom of the procedure) add these lines

    Flag = True  '<----------- Add this line of code
    Me.cboJobRef.Value = ""
    Flag = False  '<----------- Add this line of code

Then in Module 1 add this line of code

Public Flag As Boolean  '<----------- Add this line of code
Sub Picture3_Click()
UserForm1.Show vbModeless
End Sub

See if these changes help.

[Eddie1985]

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