UserForm to populate worksheet

I have tried multiple types of code, viewed countless hours of Youtube clips, and other forums, and am now at wits end!

I am trying to create a user form that will populate 13 fields (Columns) of data when the submit button is clicked. I then want the next time it is clicked to add the new data to the next row down.
I want it to start in column B (Column A is prefilled with a serial number), and then start on Row 4 Downwards.

I have included a number of combo boxes (which work), and a series of data validation (which work).

No matter what i have tried i always get an error in some form.

The Code is as follows:

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Test List")
Dim n As Long
n = sh.Range("B" & Applications.Rows.Count).End(x1Up).Row

sh.Range("B" & n + 1).Value = Me.txtTN.Value
sh.Range("C" & n + 1).Value = Me.txtTO.Value
sh.Range("D" & n + 1).Value = Me.txtPPOC.Value
sh.Range("E" & n + 1).Value = Me.txtTPOC.Value
sh.Range("F" & n + 1).Value = Me.txtAC.Value
sh.Range("G" & n + 1).Value = Me.txtSD.Value
sh.Range("H" & n + 1).Value = Me.txtED.Value
sh.Range("I" & n + 1).Value = Me.cboF1.Value
sh.Range("J" & n + 1).Value = Me.cboF2.Value
sh.Range("K" & n + 1).Value = Me.cboF3.Value
sh.Range("L" & n + 1).Value = Me.cboF4.Value
sh.Range("M" & n + 1).Value = Me.txtAP.Value
sh.Range("N" & n + 1).Value = Me.txtPJPOC.Value
sh.Range("O" & n + 1).Value = "Proposed"

End Sub


2019_08_29_08_56_27_Microsoft_Visual_Basic_for_Applications_Test_Facility_Coordination_Manager.png

Any Help with this would be greatly appreciated!!!

.
This is proven to work :

Please Login or Register  to view this content.

Logit !! Amazing, thankyou!

Follow up, if i have two user forms, that intput to two different worksheets within the same document, are there any changes required to the above?

Ie.
- UserForm1 to input to worksheet "Test List"
- UserForm 2 to input to worksheet "Asset Register"

The command buttons and text boxes all have different names to prevent any confusion.

The principle is the same, will add to each subsequent free row starting from cell B4.

Again, thanks in advance.

.
Perhaps the easiest method for you would be to copy this first macro and paste it into the second userform.

Make the first form only accessible from the sheet it will write to. Make the second form only accessible from "Test List"

Make the second form only accessible from "Asset Register".

The macro is written so it "writes" to the activesheet only. So long as the user has opened the userform while
the appropriate sheet is displayed ... you'll be ok.

There are other means of accomplishing what you want but will require a change in the macro code.

Logit, now as i dont have any coding experience, i see this could be accomplished in two ways, which of these two would work:
- specify the worksheet with: set ws = Worksheets("Test Sheet") or ("Asset Register") as appropriate, no other coding required....?
or
- following the activation of the Sub: Activate.Worksheets("Test Sheet") or ("Asset Register") as appropriate....?

thoughts?

.

Please Login or Register  to view this content.

Change "ActiveSheet" to the specific sheet name.

Again, to make it easier for you, create one macro for the TEST SHEET form, and another macro for the ASSET REGISTER form.


It would look like :

Set ws = Sheets("Test Sheet")

or

Set ws = Sheets("Asset Register")