Sending Data From User Form To First Empty Row Of Sheets

[paul_pearson]

Hi

I have come as far as I can at this point but need help with VBA code to transfer information from a form depending on which trainer is selected in the form

Best Regards

Paul


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533 (https://www.eileenslounge.com/viewtopic.php?p=312533#p312533)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499)
https://www.eileenslounge.com/viewtopic.php?p=311844#p311844 (https://www.eileenslounge.com/viewtopic.php?p=311844#p311844)
https://archive.org/download/wlsetup-all_201802/wlsetup-all.exe (https://archive.org/download/wlsetup-all_201802/wlsetup-all.exe)
https://www.eileenslounge.com/viewtopic.php?p=311826#p311826 (https://www.eileenslounge.com/viewtopic.php?p=311826#p311826)
https://www.eileenslounge.com/viewtopic.php?f=37&t=40261&p=311783#p311783 (https://www.eileenslounge.com/viewtopic.php?f=37&t=40261&p=311783#p311783)
https://www.eileenslounge.com/viewtopic.php?p=310916#p310916 (https://www.eileenslounge.com/viewtopic.php?p=310916#p310916)
https://www.eileenslounge.com/viewtopic.php?p=310720#p310720 (https://www.eileenslounge.com/viewtopic.php?p=310720#p310720)
https://www.eileenslounge.com/viewtopic.php?f=56&t=40034&p=310171#p310171 (https://www.eileenslounge.com/viewtopic.php?f=56&t=40034&p=310171#p310171)
https://www.eileenslounge.com/viewtopic.php?p=310110#p310110 (https://www.eileenslounge.com/viewtopic.php?p=310110#p310110)
https://www.eileenslounge.com/viewtopic.php?p=310024#p310024 (https://www.eileenslounge.com/viewtopic.php?p=310024#p310024)
https://www.eileenslounge.com/viewtopic.php?p=309121#p309121 (https://www.eileenslounge.com/viewtopic.php?p=309121#p309121)
https://www.eileenslounge.com/viewtopic.php?p=309101#p309101 (https://www.eileenslounge.com/viewtopic.php?p=309101#p309101)
https://www.eileenslounge.com/viewtopic.php?p=308945#p308945 (https://www.eileenslounge.com/viewtopic.php?p=308945#p308945)
https://www.eileenslounge.com/viewtopic.php?f=30&t=39858&p=308880#p308880 (https://www.eileenslounge.com/viewtopic.php?f=30&t=39858&p=308880#p308880)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[patel]

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]

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]

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]

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.

1051

[paul_pearson]

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]

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]

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]

Dear Paul,

Thank you for your kind understanding.

;)

M. Sabra

[bakerman]

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.

[paul_pearson]

Hi Bakerman

Thank you .. very good and i like your version.are you a programmer or have you taught yourself VBA

I am not very experienced yet at code but can the Time Off and Time On accept a number.I put in say 30 for Time On and 30 for Time Off but it shows 0.00 in the spreadsheet they are transferred to?

Also can there be a pop up message that a comment also must be made before sending the info

Best Regards

Paul

[bakerman]

Dear Paul

No, i'm self taught and learning heaps more every day. My username explains my profession clearly I guess :)

To include Comments with the mandatory fields goto userform designmode and select Textbox5 (comments box). Then in the Propertieswindow(bottom left window) scroll down to the Tag-property and put your message between double-quotes. Check the other objects for comparison. When sending all data the Comments-field is included in the Check Userinput loop of the code.

About your other question, what do Time - On and Time- Off represent ? Is it actual times or ... ? What do you want to see on your spreadsheet when 30 or another number is put in and data is transferred ?

I apologize for not immediatly understanding your question but English is not my native language and it's a bit rusty (quite a few years done with school so :))

Regards

[paul_pearson]

Thank you Bakerman

Please see attached.I entered in the userform 30 for Time On and 50 for Time Off but it displays 0.00 and 0.00 for Greg

Thank you again

Paul

[bakerman]

OK, but what do you actually wanna see on your spreadsheet when you enter these numbers ?? It's probably a simple format issue but which format do you require ??

[paul_pearson]

If i put 30 in the userform for Time Off i then want to see 30 in the spreadsheet for Time Off.....if i put 50 in userform for Time On then i want to see 50 in the spreadsheet for Time On...thanks

[bakerman]

.Cells(.Cells(.Rows.Count, "B").End(xlUp).Row + 1, 2).Resize(, 7) = Array(ComboBox1.Value, CInt(TextBox1.Text), _
TextBox3.Text, ComboBox3.Value, ComboBox4.Value, CInt(TextBox5.Text), TextBox4.Text)

[paul_pearson]

Thanks bakerman for all your help

[mams323]

Dear Bakerman,

Nice to see you again ;-)

Kindly find the attached file, I had prepared a Username and password form and what I want is ((to show the Userform once the file opened)) !!
Please note that the default user name is Fox and the Default Password is aa

Thank you in advance for your usual support.

[LalitPandey87]

If you want to show user form once the file opened you just need to do:


Press Alt + F11 to go to VBA window
Left hand side there is Project explorer window if you can't see it press Ctrl + R
Double Click on ThisWorkBook and paste below code



Private Sub Workbook_Open()

UserForm1.Show

End Sub

:cheers:

[mams323]

Thank you LalitPandey87 :thumbsup:

I have another issue:

Kindly be informed that I want to prepare enquiry form for staff detais, and I faced a problem with importing data from the listbox to the textboxes !! Also I don't konw hoe enable modifications and export the modified fields to the sheet again.

Please have a look to the attached file which include the from with some more detais about my needs.

I really appreciate your kind efforts :)

Thank you

M. Sabra

[bakerman]

Try this one.
If it doesn't suit your needs it's better to start your own thread so the question is picked up by more people, you might be helped sooner.

[mams323]

Well Done !! :applause:

Thank you Bakerman..

This is exactly what I need.

I wish you all the best :)

M.Sabra