CODEWITHSUNDEEP
excelvbaexcel-2016
ArcticRoyal
ArcticRoyal

Reputation: 3

Adding data from UserForm leaves first row in table empy

As the title says, I have created a UserForm that adds data to a table but for some reason it does not add the data to the first empty row. In other words there will alway be an empty row at the top of my table and I can't for the life of me figure out what I'm doing wrong. I'm pretty new to this so there is obviously something I'm doing wrong, but what?

Would love some input on this!

Here is a link to the Excel file if you want to have a look (I made a separate tab in the file that explains the relationship between the Table and the different objects): https://files.fm/u/jguvasvq

If not, here's the code:


Private Sub button_leggtil_Click()
    'Validation

    If Me.data_foretak.Value = "" Then
        MsgBox "Mangler Foretaksnavn."
    End If

    If Me.data_kontaktperson.Value = "" Then
        MsgBox "Mangler Kontaktperson."
    End If

    If Me.data_telefonnummer.Value = "" Then
        MsgBox "Mangler Telefonnummer."
    End If

    If VBA.IsNumeric(Me.data_telefonnummer.Value) = False Then
        MsgBox "Ikke et gyldig telefonnummer."
        Exit Sub
    End If

     If Me.data_epost.Value = "" Then
        MsgBox "Mangler Epost."
    End If

     If Me.data_pris.Value = "" Then
        MsgBox "Mangler Pris."
    End If

    If VBA.IsNumeric(Me.data_pris.Value) = False Then
        MsgBox "Ikke gyldig pris format."
        Exit Sub
    End If

    If Me.data_datotilb.Value = "" Then
        MsgBox "Mangler dato - Tilbud."
    End If

    If VBA.IsDate(Me.data_datotilb.Value) = False Then
        MsgBox "Feil dato format - Tilbud (Format: dd/mm/aa)."
        Exit Sub
    End If

    If Me.data_datooppf.Value = "" Then
        MsgBox "Mangler dato - Oppfølging."
    End If

    If VBA.IsDate(Me.data_datooppf.Value) = False Then
        MsgBox "Feil dato format - Oppfølging (Format: dd/mm/aa)."
        Exit Sub
    End If

    If Me.combo_sannsynlighet.Value = "" Then
        MsgBox "Mangler Sannsynlighet."
    End If


'Check for duplicate and insert data to table

Dim oNewRow As ListRow
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Tilbud").Range("TilbudTable")
rng.Select
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)

If Application.WorksheetFunction.CountIf(rng, Me.data_foretak) > 0 Then
    MsgBox "Denne bedriften finnes alerede i listen."
    Exit Sub
End If

With ws
    oNewRow.Range.Cells(1, 1).Value = Me.data_foretak
    oNewRow.Range.Cells(1, 2).Value = Me.data_kontaktperson
    oNewRow.Range.Cells(1, 3).Value = Me.data_telefonnummer
    oNewRow.Range.Cells(1, 4).Value = Me.data_epost
    oNewRow.Range.Cells(1, 5).Value = Me.data_pris
    oNewRow.Range.Cells(1, 6).Value = Me.data_datotilb
    oNewRow.Range.Cells(1, 7).Value = Me.data_datooppf
    oNewRow.Range.Cells(1, 8).Value = Me.combo_sannsynlighet
End With

' Clear Input

Me.data_foretak.Value = ""
Me.data_kontaktperson.Value = ""
Me.data_telefonnummer.Value = ""
Me.data_epost.Value = ""
Me.data_pris.Value = ""
Me.data_datotilb.Value = ""
Me.data_datooppf.Value = ""
Me.combo_sannsynlighet.Value = ""


End Sub

-------------------------------------------------------------------------------------

Private Sub button_lukk_Click()

'Close form

Unload Me

End Sub

-------------------------------------------------------------------------------------

Private Sub button_tomskjema_Click()

'Clear Form

Me.data_foretak.Value = ""
Me.data_kontaktperson.Value = ""
Me.data_telefonnummer.Value = ""
Me.data_epost.Value = ""
Me.data_pris.Value = ""
Me.data_datotilb.Value = ""
Me.data_datooppf.Value = ""
Me.combo_sannsynlighet.Value = ""

End Sub

-------------------------------------------------------------------------------------

Private Sub UserForm_Activate()

    With Me.combo_sannsynlighet
        .Clear
        .AddItem ""
        .AddItem "10%"
        .AddItem "20%"
        .AddItem "30%"
        .AddItem "40%"
        .AddItem "50%"
        .AddItem "60%"
        .AddItem "70%"
        .AddItem "80%"
        .AddItem "90%"
        .AddItem "100%"

    End With

End Sub

I appreciate any input!

Upvotes: 0

Views: 86

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

I find this code adds to a table called MyTable on Sheet1. If the table is empty, it adds as a first row. 

Sub Test()

    Dim tbl As ListObject

    Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("MyTable")

    With tbl.ListRows.Add
        .Range(tbl.ListColumns("Heading 1").Index) = "Add data in column headed 'Heading 1'"
        .Range(tbl.ListColumns("Heading 2").Index) = "Another named column."
        .Range(3) = "Third column"
    End With

End Sub

Edit - sorry, couldn't test on your code as it's not a Minimal, Reproducible Example - i.e. I couldn't easily copy it straight into my VBE to test.

Upvotes: 1

Related Questions