Submit Button / Copying Fields to Another Sheet

[gallopingant]

I created a custom user form using regular cells as inputs with a reset and submit button. The reset button works fine and erases the form. Here is the flow I want to achieve:

User inputs data into form and clicks submit. Based on the name of the country they select, it gets copied and pasted into that worksheet. The form is cleared so another entry can be made.

The submit button is where I'm having some trouble. I've attempted two ways of accomplishing this:

- Simple copying and pasting of the information
- Trying to do a With loop and count the rows

I tried to follow this tutorial but didn't have much luck:

Here is the relevant code from that video:

Sub Submit_Details()
 
  Dim shCountry As Worksheet
  Dim shForm As Worksheet
 
  Dim iCurrentRow As Integer
 
  Dim sCountryName As String
 
  Set shForm = ThisWorkbook.Sheets("Form")
 
  sCountryName = shForm.Range("H11").Value
 
  Set shCountry = ThisWorkbook.Sheets(sCountryName)
 
  iCurrentRow = shCountry.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
 
 With shCountry
 
 
    .Cells(iCurrentRow, 1) = iCurrentRow - 1
 
    .Cells(iCurrentRow, 2) = shForm.Range("H7")
 
    .Cells(iCurrentRow, 3) = shForm.Range("H9")
 
    .Cells(iCurrentRow, 4) = shForm.Range("H11")
 
    .Cells(iCurrentRow, 5) = shForm.Range("H13")
 
    .Cells(iCurrentRow, 6) = shForm.Range("H15")
 
    .Cells(iCurrentRow, 7) = Application.UserName
 
    .Cells(iCurrentRow, 8) = Format([Now()], "DD-MMM-YYYY HH:MM:SS")
 
 
 End With
 
 shForm.Range("H7, H9, H11, H13, H15").Value = ""
 
 MsgBox "Data submitted successfully!"
 
End Sub

I don't think I fully understand some of this code, specifically how he is copying and pasting it into the correct sheet. I also don't understand what is happening with setting variables as worksheets. I think if someone could explain that part, it would help me understand better.

Any help is appreciated.

 

[Trevor G]

Welcome to the Forum,

Can you upload a copy the workbook to OneDrive or another system and drop a link into your thread it will be easier to take a look at the book and the code can be adjusted and commented so it will help you with other projects.

 

[gallopingant]

This is the file from the author's website that I uploaded to the below site:

http://s000.tinyupload.com/?file_id=08157310644368977517

I am more so asking for either an explanation of what the author did or if there's an alternative way of doing this that's easier or simpler.

 

[Trevor G]

I have downloaded the workbook and I believe will only work via VBA.

There are two sets of Data Validation which are used in Cells H11 & H13. If you were to change the sheet names or add to them then you would need to adjust the validation lists. The code can be useful, but I would use a VBA UserForm, which would include picking out the sheet names and if you were to add more sheets or delete then the list would be constant. The Form sheet is only a normal worksheet and has no type of protect on it so you can do what every you like in any cell, that wouldn't be useful to users. The validation Lists are open to change. The lists are:

H11 India,China,United Kingdom,Germany,Japan,France
H13 Product 1,Product 2,Product 3,Product 4,Product 5

The code with comments.

Sub Submit_Details()
'Declared names for worksheets
    Dim shCountry As Worksheet
    Dim shForm As Worksheet
'Declared name for rows
    Dim iCurrentRow As Integer
'Declared name for Worksheet names in the Validation lists
    Dim sCountryName As String
'Set the form worksheet
    Set shForm = ThisWorkbook.Sheets("Form")
'Referencing cell H11 as the country names
    sCountryName = shForm.Range("H11").Value
'Setting the cell H11 list of names to be used for the selected country name
    Set shCountry = ThisWorkbook.Sheets(sCountryName)
'Moving to the Country name from the validation list
'Moving down column A until next empty row
    iCurrentRow = shCountry.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
'Adding the data into the country sheet that has been selected
    With shCountry
  'Adding data into each Column
        .Cells(iCurrentRow, 1) = iCurrentRow - 1
        .Cells(iCurrentRow, 2) = shForm.Range("H7")
        .Cells(iCurrentRow, 3) = shForm.Range("H9")
        .Cells(iCurrentRow, 4) = shForm.Range("H11")
        .Cells(iCurrentRow, 5) = shForm.Range("H13")
        .Cells(iCurrentRow, 6) = shForm.Range("H15")
    'Adding the username into column G
        .Cells(iCurrentRow, 7) = Application.UserName
    'Adding the Date and time into column H
        .Cells(iCurrentRow, 8) = Format([Now()], "DD-MMM-YYYY HH:MM:SS")
    End With
'Clearing the cells
    shForm.Range("H7, H9, H11, H13, H15").Value = ""
'Messsage box to confirm data has been added
    MsgBox "Data submitted successfully!"

End Sub

I hope that helps you.

 

[gallopingant]

Thank you! Your comments in the code make a lot more sense now.

What is the purpose of declaring the worksheets at the top?

 

[Trevor G]

This should help and will provide a lot of other useful information and explanations.

Creating object variables (VBA)

Office VBA reference topic

docs.microsoft.com

 

[Zoli]

Hi all

You are perfect guys. But is it possible to make submit just if all required values are filled?

 

[pixel1987]

if anyone can answer this question. ill buy you a few coffees.. just cant figure it out

 

[Zoli]

Hi all. Dont care about the question above. I make it. Thanks