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

Following is the code to close the userform from the button. Copy the code below into the Userofrm module.

Private Sub CommandButton2_Click()

    Unload Me         'Me is the generic name for the userform to which this module belongs

End Sub

Answer 3

Hi Ossie, thank you for the code. I got some errors, so I have uploaded the spreadsheet with the changes you recommended. I adjusted the columns to fit the form data (and created a time stamp column).

None of the macros seem to be working however....

The link to the form as it is now is: https://1drv.ms/u/s!Ag-WnkpUjiS9hxa0emwAtyFV4hMY

Answer 4

Andreas, thank you. I tried to copy over the code from the advanced form to mine, but it all got muddled...

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

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

Answer 5

This is the code amended, Ossie.

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("C:K")) + 1

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

        .Cells(r, "C") = Me.listGL.Value

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

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

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

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

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

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

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

        .Cells(r, "K") = Me.listTask2.Value

        .Cells(r, "L") = Me.listTask3.Value

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

    End With

End Sub