Submit Button / Copying Fields to Another Sheet

gallopingant

New Member
Joined
Aug 19, 2020
Messages
9
Office Version
  1. 365
Platform
  1. MacOS
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:
VBA Code:
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Aug 19, 2020
Messages
9
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

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

Zoli

New Member
Joined
Feb 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi all

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

pixel1987

New Member
Joined
Mar 16, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
if anyone can answer this question. ill buy you a few coffees.. just cant figure it out
 

Zoli

New Member
Joined
Feb 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi all. Dont care about the question above. I make it. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,271
Messages
5,635,211
Members
416,847
Latest member
inaramos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top