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

What you are asking is not very difficult However, it will be a lot easier to answer if you can upload a copy of your workbook to OneDrive. It will make it easy because I will have all of the ComboBox names and I am assuming that you have lists created for the ComboBoxes on the Userform.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Select "Get a Link" from the popup menu.
10. Click Copy the link and Paste into your reply on this forum.

Answer 3

Use 

Dim i as Long

With [SheetName].Range("A1")

      i = .CurrentRegion.Rows.Count

      .Offset(i, 0) = cmbDate.Value

      .Offset(i, 1) = cmbDepartment.Value

etc.

End With

It assumes the names of the controls on your form are as mentioned in the code.

To add a timestamp in the format yyyymmdd-hh:mm:ss in column i, use

     .Offset(i, 8) = Format(Date, "yyyymmdd") & "-" & Format(Time, "hh:mm:ss")

While the following articles are about the use of UserForms in Word, much of the information is relevant to the use of a Userform in any situation

See the following pages of Greg Maxey's website :

http://gregmaxey.mvps.org/Create\_and\_employ\_a\_UserForm.htm

http://gregmaxey.mvps.org/Populate\_UserForm\_ListBox.htm

http://gregmaxey.mvps.org/Customize\_Ribbon.htm

See the "Cascading Listboxes" section  the following page of Greg Maxey's website:

http://gregmaxey.mvps.org/word_tip_pages/populate_userForm_listbox_or_combobox.html

Answer 4

I know very little VBA and would appreciate some help.

Have a look into this file:

https://dl.dropboxusercontent.com/u/35239054/Simple_Userform.xlsm

Try the simple one first, then the advanced one.

Andreas.

Answer 5

Hi, 

1. Thank you for your response and code, but I am getting this error (I added the code to the date combo box):