Sending Data From User Form To First Empty Row Of Sheets

**paul_pearson** · 07-27-2013, 02:08 PM

**patel** · 07-27-2013, 07:47 PM

Code:

Private Sub CommandButton1_Click()
shname = ComboBox2.Text
With Sheets(shname)
  LR = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
  .Cells(LR, 2) = ComboBox1.Text
  .Cells(LR, 3) = TextBox1.Text
  .Cells(LR, 4) = TextBox3.Text
  .Cells(LR, 5) = ComboBox3.Text
  .Cells(LR, 6) = ComboBox4.Text
  .Cells(LR, 7) = TextBox5.Text
  .Cells(LR, 8) = TextBox4.Text
End With
End Sub

**paul_pearson** · 07-28-2013, 03:22 PM

very nice. Thank you

I have a further request please

1. can the code work in a way so that the userform must be filled in from the top box (date) in order to the bottom box (comment).Options in the drop-down lists are only visible if the box above has an entry.Example:To see any names in the Trainer drop-down list is only visible if the date box has been filled
2. The information can only be transferred if all box`s in the userform are filled in
3. Once info transferred then the userform data is deleted but the userform remains open

Any help again appreciated

Regards

Paul

**patel** · 07-28-2013, 10:20 PM

Code:

Private Sub CommandButton1_Click()
If ComboBox1.Text = "" Or TextBox1.Text = "" Or TextBox3.Text = "" Or _
  ComboBox3.Text = "" Or ComboBox4.Text = "" Or TextBox5.Text = "" Or _
  ComboBox2.Text = "" Then

  MsgBox "some box empty"
  Exit Sub
End If
  
shname = ComboBox2.Text
With Sheets(shname)
  LR = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
  .Cells(LR, 2) = ComboBox1.Text
  .Cells(LR, 3) = TextBox1.Text
  .Cells(LR, 4) = TextBox3.Text
  .Cells(LR, 5) = ComboBox3.Text
  .Cells(LR, 6) = ComboBox4.Text
  .Cells(LR, 7) = TextBox5.Text
  .Cells(LR, 8) = TextBox4.Text
  ComboBox1.Text = ""
  TextBox1.Text = ""
  TextBox3.Text = ""
  ComboBox2.Text = ""
  ComboBox3.Text = ""
  ComboBox4.Text = ""
  TextBox5.Text = ""
  TextBox4.Text = ""
End With
End Sub

**mams323** · 07-29-2013, 02:45 AM

Thank you Patel

Well Done !!

Could you please have a look to the attached file, I had added new userform to be used for serching and modifying the entered data.
It would be appreciated if you helped me how to import the entered data from the table on the sheet to the listbox to be modified, and what is the code for accept changes and export the data again to the same table on the sheet.

Thank you in advance for your geart efforts.

USERFORM(1).xlsm

**paul_pearson** · 07-29-2013, 04:33 AM

Thanks Patel

Hi mams323
I do not mind if you use my spreadsheet but you may get more responces if you start your own thread

Thanks

Paul

**mams323** · 07-29-2013, 11:25 AM

Dear Paul,

First I would like to thank you for your kind understanding.

Actually I found your spreadsheet very useful to descripe my problem.

I was supposed to ask you first to allow me before using your file, really I am sory for that.

Appreciated.

Dear Patel,
Please ignore my request

Thank You All.

M. Sabra (mams323)

**paul_pearson** · 07-29-2013, 11:39 AM

Hi M. Sabra

You are most welcome to use the spreadsheet anytime to suit your needs but i just thought you might get a better responce to your question if you started your own thread using the spreadsheet.

Good luck with your project

Paul

**mams323** · 07-29-2013, 11:58 AM

Dear Paul,

Thank you for your kind understanding.

M. Sabra

**bakerman** · 07-30-2013, 10:14 AM

Paul, some coding tips

1.Preferably use the List-method to fill Combobox instead of AddItem-method

2.Much easier way to check for user-input is to use the Tag-proprty of each object. Especially when using larger amounts of objects on your userform it's much more compact than listing them one by one.

3.Write data in one time to sheet instead of writing them object by object.

4.Use TypeName-properety to clear fields instead of listing them one by one and clearing.

Remember that every line of code has to be read before execution so the less lines in your code the faster it will be executed.