Userform unable to use

[Mike_CS]

Hi

Can anybody put me right on a 'Compile error: Sub or Function not defined' please.

Have had my first try at a userform and have copied this from the web.

The userform has many labels and text boxes and a few combo boxes. Basically i'm looking for the form to populate a table called "datastore" which is on the "datastore" tab.

i get the above error message which also highlights the phrase "offset" on row 8. Can anybody give me a clue as i've drawn a complete blank (the code im using is below).

Apologies if this has been answered a hundred times. i cannot find anything that works.

Private Sub CommandButton1_Click()

    'Copy input values to sheet.
    Dim oNewRow As ListRow
    Dim rng As Range


    
    Set rng = ThisWorkbook.Worksheets("Datastore").Range("datastore")
    rng.Select
    Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
    With ws
        oNewRow.Range.Cells(1, 1).value = Offset(0, -1).value + 1
        oNewRow.Range.Cells(1, 2).value = Me.Label2.value
        oNewRow.Range.Cells(1, 3).value = Me.Label13.value
        oNewRow.Range.Cells(1, 4).value = Me.TextBox1.value
        oNewRow.Range.Cells(1, 5).value = Me.Label18.value
        oNewRow.Range.Cells(1, 6).value = Me.Label15.value
        oNewRow.Range.Cells(1, 8).value = Me.ComboBox3.value
        oNewRow.Range.Cells(1, 9).value = Me.TextBox2.value
        oNewRow.Range.Cells(1, 10).value = Me.ComboBox2.value
        oNewRow.Range.Cells(1, 11).value = Me.Label16.value
        oNewRow.Range.Cells(1, 12).value = Me.Label19.value
        oNewRow.Range.Cells(1, 13).value = Me.Label15.value
        oNewRow.Range.Cells(1, 18).value = Me.Label15.value
        oNewRow.Range.Cells(1, 16).value = Me.Label2.value
        oNewRow.Range.Cells(1, 17).value = Me.Label13.value


    End With


'    Clear input controls.
'    Clear_create_form
    
End Sub

 

[steve the fish]

You need to offset from a range. rng.Offset or Selection.Offset for example.

 

[Mike_CS]

You need to offset from a range. rng.Offset or Selection.Offset for example.

Steven. I love you and want to have your babies ha
it has now moved on to the next line but i am now getting a "compile error: method or data member not found" and highlighting the .value part now. I have changed the code a little

Private Sub cmdSubmit_Click()
    
    'This button will be used to add new entry to database and email details to person allocated


    'Copy input values to sheet.
    Dim oNewRow As ListRow
    Dim rng As Range


    
    Set rng = ThisWorkbook.Worksheets("Datastore").Range("datastore")
    rng.Select
    
    Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
    With ws
        oNewRow.Range.Cells(1, 1).value = rng.Offset(0, -1).value + 1
        oNewRow.Range.Cells(1, 2).value = Me.lblAlloc8rFullnameCR.value
        oNewRow.Range.Cells(1, 3).value = Me.lblAlloc8rPIDCR.value
        oNewRow.Range.Cells(1, 4).value = Me.txtRequestor.value
        oNewRow.Range.Cells(1, 5).value = Me.lblDateRec.value
        oNewRow.Range.Cells(1, 6).value = Me.lblTodayCR.value
        oNewRow.Range.Cells(1, 8).value = Me.cmbReqType.value
        oNewRow.Range.Cells(1, 9).value = Me.txtReqDetails.value
        oNewRow.Range.Cells(1, 10).value = Me.cmbAlloc8d2Name.value
        oNewRow.Range.Cells(1, 11).value = Me.lblAlloc8d2PID.value
        oNewRow.Range.Cells(1, 12).value = Me.lblToBDoneBy.value
        oNewRow.Range.Cells(1, 13).value = Me.lblTodayCR.value
        oNewRow.Range.Cells(1, 18).value = Me.lblTodayCR.value
        oNewRow.Range.Cells(1, 16).value = Me.lblAlloc8rFullnameCR.value
        oNewRow.Range.Cells(1, 17).value = Me.lblAlloc8rPIDCR.value


    End With


'    Clear input controls.
'    Clear_create_form
    
End Sub

Is this a pile of rubbish what i have written?

 

[Mike_CS]

This now seems to work for a self contained dataset with userform

Sub Insert_record()

    'Copy input values to sheet.

    Dim lblAlloc8rFullnameCR As Label
    Dim lblAlloc8rPIDCR As Label
    Dim txtRequestor As Textbox
    Dim lblDateRec As Label
    Dim lblTodayCR As Label
    Dim cmbReqType As Combobox
    Dim txtReqDetails As Textbox
    Dim cmbAlloc8d2Name As Combobox
    Dim lblAlloc8d2PID As Label
    Dim lblToBDoneBy As Label
    Dim the_sheet As Worksheet
    Dim table_list_object As ListObject
    Dim table_object_row As ListRow

    Set the_sheet = Sheets("datastore")
    Set table_list_object = the_sheet.ListObjects(1)
    Set table_object_row = table_list_object.ListRows.Add

        table_object_row.Range.Cells(1, 2).Value = FrmCreateRequest.lblAlloc8rFullnameCR
        table_object_row.Range.Cells(1, 3).Value = FrmCreateRequest.lblAlloc8rPIDCR
        table_object_row.Range.Cells(1, 4).Value = FrmCreateRequest.txtRequestor
        table_object_row.Range.Cells(1, 5).Value = FrmCreateRequest.lblDateRec
        table_object_row.Range.Cells(1, 6).Value = FrmCreateRequest.lblTodayCR
        table_object_row.Range.Cells(1, 8).Value = FrmCreateRequest.cmbReqType
        table_object_row.Range.Cells(1, 9).Value = FrmCreateRequest.txtReqDetails
        table_object_row.Range.Cells(1, 10).Value = FrmCreateRequest.cmbAlloc8d2Name
        table_object_row.Range.Cells(1, 11).Value = FrmCreateRequest.lblAlloc8d2PID
        table_object_row.Range.Cells(1, 12).Value = FrmCreateRequest.lblToBDoneBy
        table_object_row.Range.Cells(1, 13).Value = FrmCreateRequest.lblTodayCR
        table_object_row.Range.Cells(1, 18).Value = FrmCreateRequest.lblTodayCR
        table_object_row.Range.Cells(1, 16).Value = FrmCreateRequest.lblAlloc8rFullnameCR
        table_object_row.Range.Cells(1, 17).Value = FrmCreateRequest.lblAlloc8rPIDCR

End Sub

Also worked out how to get it to write to an external workbook table.

Sub Insert_record2Standalone()
   
    'Copy input values to sheet.
    
    Dim lblAlloc8rFullnameCR As Label
    Dim lblAlloc8rPIDCR As Label
    Dim txtRequestor As Textbox
    Dim lblDateRec As Label
    Dim lblTodayCR As Label
    Dim cmbReqType As Combobox
    Dim txtReqDetails As Textbox
    Dim cmbAlloc8d2Name As Combobox
    Dim lblAlloc8d2PID As Label
    Dim lblToBDoneBy As Label
    Dim the_book As Workbook
    Dim the_sheet As Worksheet
    Dim table_list_object As ListObject
    Dim table_object_row As ListRow
    
Application.ScreenUpdating = False
    
    Set the_book = Workbooks.Open(Filename:="your full filepath\name and extension")
    Set the_sheet = Sheets("name of tab with table")
    Set table_list_object = the_sheet.ListObjects(1)
    Set table_object_row = table_list_object.ListRows.Add
    
        table_object_row.Range.Cells(1, 2).Value = FrmCreateRequest.lblAlloc8rFullnameCR
        table_object_row.Range.Cells(1, 3).Value = FrmCreateRequest.lblAlloc8rPIDCR
        table_object_row.Range.Cells(1, 4).Value = FrmCreateRequest.txtRequestor
        table_object_row.Range.Cells(1, 5).Value = FrmCreateRequest.lblDateRec
        table_object_row.Range.Cells(1, 6).Value = FrmCreateRequest.lblTodayCR
        table_object_row.Range.Cells(1, 8).Value = FrmCreateRequest.cmbReqType
        table_object_row.Range.Cells(1, 9).Value = FrmCreateRequest.txtReqDetails
        table_object_row.Range.Cells(1, 10).Value = FrmCreateRequest.cmbAlloc8d2Name
        table_object_row.Range.Cells(1, 11).Value = FrmCreateRequest.lblAlloc8d2PID
        table_object_row.Range.Cells(1, 12).Value = FrmCreateRequest.lblToBDoneBy
        table_object_row.Range.Cells(1, 13).Value = FrmCreateRequest.lblTodayCR
        table_object_row.Range.Cells(1, 18).Value = FrmCreateRequest.lblTodayCR
        table_object_row.Range.Cells(1, 16).Value = FrmCreateRequest.lblAlloc8rFullnameCR
        table_object_row.Range.Cells(1, 17).Value = FrmCreateRequest.lblAlloc8rPIDCR
        
    the_book.Save
    the_book.Close
    
    MsgBox "Information Sucessfully Updated"


End Sub

Thanks for making me think a little different. got there in the end.