Userform code help - submission add row above

[Jlombard_]

[FONT=&quot]Hi there guys.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Building a user form currently and as it stands the data gets added to the last blank row:[/FONT]
[FONT=&quot]To achieve the last row input I've used the offset method and this target row code:[/FONT]
[FONT=&quot]"Dim TargetRow As Integer[/FONT]
[FONT=&quot]TargetRow = Sheets("engine").Range("B3").Value + 1"[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]when clicking the submit button; What i need is a piece of code to add a new row to the top, which the new data is then inputted into so that new submissions are always on top.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]If you need to look at the rest of the code let me know![/FONT]

 

[dmt32]

Hi,
maybe

Range("2:2").Insert CopyOrigin:=xlFormatFromRightOrBelow

[FONT=&quot]example taken fron VBA helpfile, inserts a row above row 2, copying the format from the row below (row 3) instead of from the header row

adjust as required to meet your project need

Dave


[/FONT]

 

[Jlombard_]

Hi,
maybe

Range("2:2").Insert CopyOrigin:=xlFormatFromRightOrBelow

example taken fron VBA helpfile, inserts a row above row 2, copying the format from the row below (row 3) instead of from the header row

adjust as required to meet your project need

Dave

Hi Dave,

Thanks for the reply! the code works brilliantly at inserting new rows like I wanted, however I can't seem to make the data pull into the new row that's been created.

Private Sub CMD_Submit_All_BTN_click()

Dim TargetRow As Integer
Dim FullName As String 'full name
Dim PolicyNumber As String






TargetRow = Sheets("engine").Range("B3").Value + 1 'this determines where the next entry will go in the spreadsheet
FullName = txt_customer_firstname & " " & txt_customer_surname
PolicyNumber = txt_policy_ref


If Application.WorksheetFunction.CountIf(Sheets("Data").Range("X8:x80"), PolicyNumber) > 0 Then


MsgBox PolicyNumber & " has already been submitted for an AOK", 0, "check"
Exit Sub






End If


Range("C8:AF8").Insert CopyOrigin:=xlFormatFromRightOrBelow




'''BEGIN INPUT DATA INTO DATABASE'''
                                                                                                                                    '''EMPLOYEE DETAILS'''
Sheets("Data").Range("Data_Start").Offset(TargetRow, 0).Value = txt_auto_date 'timestamp of day form is submitted
Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value = txt_employee_name 'employees full name
Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value = CB_department 'Which department the employee is from
Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value = txt_email_address 'what is the employee email address

Any ideas on what I'd need to change? still pretty new to this!

 

[Jlombard_]

Hi Dave,

Thanks for the reply! the code works brilliantly at inserting new rows like I wanted, however I can't seem to make the data pull into the new row that's been created.

Private Sub CMD_Submit_All_BTN_click()

Dim TargetRow As Integer
Dim FullName As String 'full name
Dim PolicyNumber As String






TargetRow = Sheets("engine").Range("B3").Value + 1 'this determines where the next entry will go in the spreadsheet
FullName = txt_customer_firstname & " " & txt_customer_surname
PolicyNumber = txt_policy_ref


If Application.WorksheetFunction.CountIf(Sheets("Data").Range("X8:x80"), PolicyNumber) > 0 Then


MsgBox PolicyNumber & " has already been submitted for an AOK", 0, "check"
Exit Sub






End If


Range("C8:AF8").Insert CopyOrigin:=xlFormatFromRightOrBelow




'''BEGIN INPUT DATA INTO DATABASE'''
                                                                                                                                    '''EMPLOYEE DETAILS'''
Sheets("Data").Range("Data_Start").Offset(TargetRow, 0).Value = txt_auto_date 'timestamp of day form is submitted
Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value = txt_employee_name 'employees full name
Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value = CB_department 'Which department the employee is from
Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value = txt_email_address 'what is the employee email address

Any ideas on what I'd need to change? still pretty new to this!

Scratch that, figured it out lol!

 

[dmt32]

Scratch that, figured it out lol!

Hi,
glad resolved.

just as tip, you should not need to keep declaring the worksheet / range you are writing data to.

you can change this

Sheets("Data").Range("Data_Start").Offset(TargetRow, 0).Value = txt_auto_date 'timestamp of day form is submitted
Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value = txt_employee_name 'employees full name
Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value = CB_department 'Which department the employee is from
Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value = txt_email_address 'what is the employee email address

to this

With Sheets("Data").Range("Data_Start")
        .Offset(TargetRow, 0).Value = txt_auto_date 'timestamp of day form is submitted
        .Offset(TargetRow, 1).Value = txt_employee_name 'employees full name
        .Offset(TargetRow, 2).Value = CB_department 'Which department the employee is from
        .Offset(TargetRow, 3).Value = txt_email_address 'what is the employee email address
    End With

which as you can see, only uses the worksheet / range once & makes code easier to read.

You can read up on With Statement in VBA helpfile.

there are various other ways you can write data from userform to worksheet but example sticks with your current approach,

Hope helpful

Dave