VBA User form error when saving data to a table

[nicic]

Hi all,

Hoping you can help. Been a long time (a few decades) since I've played with excel properly. I need some spreadsheets doing, so thought I'd throw myself back in. I've been trying to make a user form using a tutorial and changing for my own needs. It works well (needs prettying up), but when I save data from form to the table, sometimes it works and sometimes it throws an error. It's driving me mad. Can anyone have a look for me?
Thanks so much..

error.JPG

Code:

First form:

Private Sub buttonclose_Click()
    Unload Me
    End Sub

Private Sub buttonDelete1_Click()
Dim answer As Integer
answer = MsgBox("Are you sure you wnat to delete this entry?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete")
If answer = vbYes Then Call buttonDelete2
End Sub

Private Sub buttonDelete2()
Call DeleteSelectedRow(AllergenDB.ListIndex)
End Sub

Private Sub buttonNew_Click()
Dim frm As New Enternewdata
    frm.Show vbModal
    ' Refill the ListBox with data so the new staff member is included
    Call AddDataToListbox1
   End Sub

Private Sub UserForm_Initialize()
    ' Fill the listbox
   Call AddDataToListbox1
End Sub

MAIN SUBS

' Add the data to the ListBox using RowSource
Private Sub AddDataToListbox1()
' Get the data range
    Dim rg As Range
    Set rg = GetRange1
' Link the data to the ListBox
    With AllergenDB
        .RowSource = rg.Address(External:=True)
        .ColumnCount = rg.Columns.Count
        .ColumnWidths = "80"
        .ColumnHeads = True
        .ListIndex = 0
    End With
End Sub

New Data Form:

Private Sub CommandCloseNew_Click()
Unload Me
End Sub

' Save the record and clear the data from the controls
Private Function WriteDataToSheet1()
Dim newRow As Long
    With Allergen_Database
' Get the first blank row of data
        newRow = .Cells(.Rows.Count, 1).End(xlUp).row + 1
' Write the data
        .Cells(newRow, 1).Value = TextBox1Name.Value
        .Cells(newRow, 2).Value = Textbox2.Value
        .Cells(newRow, 3).Value = Textbox3.Value
        .Cells(newRow, 4).Value = TextBox4.Value
        .Cells(newRow, 5).Value = TextBox5.Value
        .Cells(newRow, 6).Value = TextBox6.Value
        .Cells(newRow, 7).Value = TextBox7.Value
        .Cells(newRow, 8).Value = TextBox8.Value
        .Cells(newRow, 9).Value = TextBox9.Value
        .Cells(newRow, 10).Value = TextBox10.Value
        .Cells(newRow, 11).Value = TextBox11.Value
        .Cells(newRow, 12).Value = TextBox12.Value
        .Cells(newRow, 13).Value = TextBox13.Value
        .Cells(newRow, 14).Value = TextBox14.Value
        .Cells(newRow, 15).Value = TextBox15.Value
        .Cells(newRow, 16).Value = TextBox16.Value
        .Cells(newRow, 17).Value = TextBox17.Value
        .Cells(newRow, 18).Value = TextBox18.Value
        .Cells(newRow, 19).Value = TextBox19.Value
        .Cells(newRow, 20).Value = TextBox20.Value
        .Cells(newRow, 21).Value = TextBox21.Value
        .Cells(newRow, 22).Value = TextBox22.Value
        .Cells(newRow, 23).Value = TextBox23.Value
        .Cells(newRow, 24).Value = TextBox24.Value
        .Cells(newRow, 25).Value = TextBox25.Value
        .Cells(newRow, 26).Value = TextBox26.Value
        .Cells(newRow, 27).Value = TextBox27.Value
End With
End Function

' Clear data from the textbox controls
Public Sub EmptyTextboxes()
Dim c As Control
    ' Read through all the controls
    For Each c In Me.Controls
    If TypeName(c) = "TextBox" Then
        c.Value = ""
    End If
    Next
End Sub


Private Sub CommandSaveNew_Click()
If MsgBox("Do you want to save this record?:", vbYesNo, "Save record") = vbYes Then
    Call WriteDataToSheet1
    ' Remove data from textboxes
    Call EmptyTextboxes
End If
End Sub

Module:

Public Function GetRange1() As Range
' Get the data range from the Staff worksheet
    Set GetRange1 = Allergen_Database.Range("A2").CurrentRegion
    ' remove the header from the range by moving the range down one row and
    ' then removing the last row.
   Set GetRange1 = GetRange1.Offset(1).Resize(GetRange1.Rows.Count - 1)
End Function


' Delete the row from the staff worksheet
Public Sub DeleteSelectedRow(ByVal row As Long)
    ' Offset moves the range a given number of rows
    Allergen_Database.Range("A2").Offset(row).EntireRow.Delete
End Sub

[June7]

Please post code between CODE tags to retain indentation and readability. Can edit your post.

Why not use Access or some other relational database app?

How do I trigger the user form?

Sheets show #NAME? errors.

[nicic]

Please post code between CODE tags to retain indentation and readability. Can edit your post.

Why not use Access or some other relational database app?

How do I trigger the user form?

Sheets show #NAME? errors.

Thanks -
I worked out the error, was related to the the fact I was using a table.
I've managed to link it to the table now instead of a range. So it's now working
I just now need to figure delete and edit... and add headers to list box...
everyday is a learning day

[Aussiebear]

Nicic , if you are satisfied that the thread is solved, can you go to Thread Tools and select "Mark this thread as Solved" please?