Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Sending Data From User Form To First Empty Row Of Sheets

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    Last edited by DocAElstein; 04-07-2024 at 12:51 PM.

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    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

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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

  4. #4
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    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

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    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
    Last edited by mams323; 07-29-2013 at 02:51 AM.

  6. #6
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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

  7. #7
    Junior Member
    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    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)

  8. #8
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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

  9. #9
    Junior Member
    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Dear Paul,

    Thank you for your kind understanding.



    M. Sabra

  10. #10
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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.
    Attached Files Attached Files

Similar Threads

  1. Replies: 5
    Last Post: 07-11-2013, 07:31 AM
  2. Delete Entire Row For All Empty Cells In Column
    By johnreid7477 in forum Excel Help
    Replies: 4
    Last Post: 06-15-2013, 05:50 AM
  3. Macro sending with mailto linkup to other sheets
    By ragil.yudy in forum Excel Help
    Replies: 2
    Last Post: 10-23-2012, 02:42 PM
  4. Replies: 2
    Last Post: 09-24-2012, 11:19 PM
  5. Adding A Menu Bar To A User Form
    By Rasm in forum Excel Help
    Replies: 14
    Last Post: 05-05-2011, 04:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •