Add to external Excel database

[COwen]

I am trying to add to add date from a Userform on one workbook to another workbook that holds my data. Here is the code I have currently:

Private Sub imgGenerate_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim x As Integer
With Sheet12 'Worksheets("Sample Router")
    .Range("INV").Value = cntrl5.Value
    .Range("SON").Value = cntrl6.Value
    .Range("Cust").Value = cntrl8.Value
    .Range("IDate").Value = cntrl7.Value
    .Range("EDate").Value = cntrl4.Value
    .Range("RouterX").Value = (.Range("RouterX").Value + 1)
    '.Range("A1:O62").PrintOut
End With
On Error GoTo Err
Application.ScreenUpdating = False

    With Application
        .DisplayAlerts = False
        .Visible = False
        .ScreenUpdating = False
    End With
    
    Set wb = Workbooks.Open(Filename:="C:\Users\caleb.owen\Documents\Cordial FileTracker tobezip\XLDataBaseforCordial.xlsm")
    Set ws = wb.Worksheets("Sheet1")
    
    'find first empty row in database
    'iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    'copy the data to the database
    ws.Cells(iRow, 0).Value = Me.cntrl1.Value
    ws.Cells(iRow, 2).Value = Me.cntrl2.Value
    ws.Cells(iRow, 3).Value = Me.cntrl5.Value
    ws.Cells(iRow, 4).Value = Me.cntrl6.Value
    ws.Cells(iRow, 5).Value = Me.cntrl7.Value
    ws.Cells(iRow, 6).Value = Me.cntrl8.Value
    ws.Cells(iRow, 7).Value = Me.cntrl4.Value
    ws.Cells(iRow, 8).Value = Me.cntrl9.Value
    ws.Cells(iRow, 9).Value = Me.cntrl3.Value
    ws.Cells(iRow, 10).Value = Me.cntrl10.Value
Err:
    Workbooks("XLDataBaseforCordial.xlsm").Close True
    
With Sheet12 'Worksheets("Sample Router")
    .Range("INV").Value = ""
    .Range("SON").Value = ""
    .Range("Cust").Value = ""
    .Range("IDate").Value = ""
    .Range("EDate").Value = ""
End With
cntrl2.Value = Sheet12.Range("RouterV").Value
For x = 5 To 10
Me.Controls("cntrl" & x).Value = ""
Next x
End Sub

It is not populating correctly at all. It will only add the data to the first line then it will stop.

 

[Fluff]

Try removing the error handler, that way you can see if there are any errors.
I'm surprised anything gets added to the database

 

[COwen]

I am curious why you are surprised?

 

[Fluff]

Because I would have though the error handler would kick-in before anything is written to the database sheet.

 

[COwen]

I did get an error - 1004. On this ws.Cells(iRow, 0).Value = Me.cntrl1.Value line of code.

 

[COwen]

Why is that? I am not greatly experienced in VBA -- only do I know some weird, quirky things.

 

[Fluff]

You cannot have a column 0, it should presumably be

ws.Cells(iRow, 1)

 

[COwen]

What is the "COLOR?" That does not belong in the code.

 

[COwen]

I do not get the error anymore

 

[COwen]

...now, it just crashes