VBA User Form Command Button - add entry to next row - Excel 2013

Question

Hello,

I have struggled through many youtube videos trying to find the code for a command button to add an entry from a user form to an excel spreadsheet, and another command button (Close form) to close the form. Most of the online tutorials are using Excel 2010, I have the latest 2013 version of Excel however, and some things appear to be different between the Excel versions...

I know very little VBA and would appreciate some help.

Please find my form attached:

I wold like the records added to go to the 2nd row in sheet below:

Also, does anyone know how to add a time stamp to each entry? I would like another column added so that the time of entry of each form is displayed. Is this possible?...

Appreciate it!

Answer 1

I think it is easier to ammend my one to include some of the features.. Here is my form as it is now...

Well, easier is the code that you understand. You are the developer, not me. I can only make suggestions. ;-)

Have a look into this file:

https://dl.dropboxusercontent.com/u/35239054/Samples/5bef207c-9d0f-417f-b56a-cfab0ea75198.xlsm

Andreas.

Answer 2

Hi Ossie, thank you for the instructions. I have now uploaded my workbook, here is  the link:

https://1drv.ms/u/s!Ag-WnkpUjiS9hxXsh2ePp8ESo5hw

Answer 3

Thank you Andreas! The simple form example got my 'close form' button working, but not my 'add' command button working...

Answer 4

Thank you Andreas! The simple form example got my 'close form' button working, but not my 'add' command button working...

Have a look into the Advanced sheet, that form has an Add button.

Andreas.

Answer 5

Not all of the column headers appear to align with the fields in the Userform but I am sure that the following code will give you the idea of how to achieve your results.

Copy the following UDF (User Defined Function) into your standard Module1. This UDF is called to find the last used row of a range (In your case columns A to J) in the Data sheet and then the code adds 1 for the next blank row.

Function LastRowOrCol(bolRowOrCol As Boolean, Optional rng As Range) As Long

    'Finds the last used row or column in a worksheet

    'First parameter is True for Last Row or False for last Column

    'Third parameter is optional

        'Must be specified if not ActiveSheet

    Dim lngRowCol As Long

    Dim rngToFind As Range

    If rng Is Nothing Then

        Set rng = ActiveSheet.Cells

    End If

    If bolRowOrCol Then

        lngRowCol = xlByRows

    Else

        lngRowCol = xlByColumns

    End If

    With rng

        Set rngToFind = rng.Find(What:="*", _

                LookIn:=xlFormulas, _

                LookAt:=xlPart, _

                SearchOrder:=lngRowCol, _

                SearchDirection:=xlPrevious, _

                MatchCase:=False)

    End With

    If Not rngToFind Is Nothing Then

        If bolRowOrCol Then

            LastRowOrCol = rngToFind.Row

        Else

            LastRowOrCol = rngToFind.Column

        End If

    End If

End Function

Copy the following code into the Userform Module.

Private Sub CommandButton1_Click()

    Dim wsData As Worksheet

    Dim r As Long

    Set ws = Worksheets("Data") 'Assign worksheet name to a worksheet variable

    'Because "With ws" is used prefix all cells with dot as follows

    'otherwise requires ws.cells(r, "C") etc

    With ws

        r = LastRowOrCol(True, .Columns("A:J")) + 1

        .Cells(r, "B") = Time() 'Time stamp

        .Cells(r, "C") = DateValue(Me.listDate.Value)    'Converts text  in the Userform to date format

        .Cells(r, "D") = Me.listDepartment.Value

        .Cells(r, "E") = Me.listEmployeeNo.Value

        .Cells(r, "F") = Me.listEmployee.Value

        .Cells(r, "G") = Me.listProjectName.Value

        .Cells(r, "I") = Me.listTask1.Value

        .Cells(r, "J") = Me.listHours.Value

    End With

End Sub

Feel free to get back to me with any problems. However, it will be tomorrow before I get back to you again because it is approaching bed time in my part of the world.