Add blank row in middle of data and paste userform values to it

[PSkinny]

This is my first post so please be gentle with me.
I am currently using Excel for Office 365.

I have a user form for data entry to either of two sheets, the sheet is selected in ComboBox5.

Sub EditAdd finds last row of the existing data (using column C as the reference) and puts the values of the Text and Combo boxes into the empty row below it in the required columns.
This part works.

[B]Sub EditAdd()[/B]
    Dim strName As String, ws1 As Worksheet, lRow As Long
    strName = ComboBox5.Value
    On Error Resume Next
    Set ws1 = Worksheets(strName)
   
    If Not ws1 Is Nothing Then 'sheet exist
        Sheets(strName).Select
    End If
 
    lRow = ws1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
       
        With ws1
            ThisWorkbook.Worksheets("QLD Schedule").Protect Password:="", _
            UserInterfaceOnly:=True
            ThisWorkbook.Worksheets("NSW Schedule").Protect Password:="", _
            UserInterfaceOnly:=True
 
            .Cells(lRow, 2).Value = Me.ComboBox1.Value
            .Cells(lRow, 3).Value = Me.ComboBox2.Value
            .Cells(lRow, 4).Value = Me.TextBox3.Value
            .Cells(lRow, 5).Value = Me.TextBox4.Value
            .Cells(lRow, 6).Value = Me.ComboBox3.Value
            .Cells(lRow, 8).Value = Me.ComboBox4.Value
        End With
    Set ws1 = Nothing
[B]End Sub
[/B]

SubEditAdd2 creates a blank row below the cursors current position (somewhere in the middle of existing data), I then need it to put the values of the Text and Combo boxes into the new blank row in the correct columns.
This part does not work.
The blank row is being created; however, the values of the Text and Combo boxes are not being added to the sheet. I am trying to use C6 as the starting cell for the xlShiftDown.
No errors are generated.
Any assistance would be gratefully accepted.

[B]Sub EditAdd2()[/B]
    Dim strName As String, ws1 As Worksheet, eRow As Range
    strName = ComboBox5.Value
    On Error Resume Next
    Set ws1 = Worksheets(strName)
   
    If Not ws1 Is Nothing Then 'sheet exists
        Sheets(strName).Select
    End If
   
    'Inserts a blank row below cursor
        ActiveCell.Offset(1).EntireRow.Insert Shift:=xlShiftDown
 
        With ws1
        ThisWorkbook.Worksheets("QLD Schedule").Protect Password:="", _
        UserInterfaceOnly:=True
       
        ThisWorkbook.Worksheets("NSW Schedule").Protect Password:="", _
        UserInterfaceOnly:=True
       
        eRow = Range("C6").End(xlDown).Offset(1, 0).Row + 1
 
            .Cells(eRow, 2).Value = Me.ComboBox1.Value
            .Cells(eRow, 3).Value = Me.ComboBox2.Value
            .Cells(eRow, 4).Value = Me.TextBox3.Value
            .Cells(eRow, 5).Value = Me.TextBox4.Value
            .Cells(eRow, 6).Value = Me.ComboBox3.Value
            .Cells(eRow, 8).Value = Me.ComboBox4.Value
       End With
    Set ws1 = Nothing
[B]End Sub
[/B]

 

[Fluff]

How about

Sub EditAdd2()
   Dim strName As String, ws1 As Worksheet
   strName = ComboBox5.Value
   
   If Evaluate("isref('" & strName & "'!A1)") Then
      Set ws1 = Worksheets(strName)
   Else
      MsgBox "Sheet " & strName & " doesn't exist"
      Exit Sub
   End If

   With ws1
      .Protect Password:="", UserInterfaceOnly:=True
      .Range("C7").EntireRow.Insert
      .Cells(7, 2).Value = Me.ComboBox1.Value
      .Cells(7, 3).Value = Me.ComboBox2.Value
      .Cells(7, 4).Value = Me.TextBox3.Value
      .Cells(7, 5).Value = Me.TextBox4.Value
      .Cells(7, 6).Value = Me.ComboBox3.Value
      .Cells(7, 8).Value = Me.ComboBox4.Value
   End With
    Set ws1 = Nothing
End Sub

 

[PSkinny]

Thanks Fluff, I will give it a try and let you know how it goes.

 

[PSkinny]

Hi Fluff
Thanks for the quick response. Your code works well however it always inserts at row 7 and I need it to insert at the cursor. My original attempt inserted a blank row then searched down from c6 to find that row and insert the data, well that was the plan but it didn't insert the data.

 

[Fluff]

Ok, how about

Sub EditAdd2()
   Dim strName As String, ws1 As Worksheet
   Dim NewRw As Long
   strName = ComboBox5.Value
   
   If Evaluate("isref('" & strName & "'!A1)") Then
      Set ws1 = Worksheets(strName)
   Else
      MsgBox "Sheet " & strName & " doesn't exist"
      Exit Sub
   End If

   ws1.Select
   ws1.Protect Password:="", UserInterfaceOnly:=True
   NewRw = ActiveCell.Row
   Rows(NewRw).Insert
   Cells(NewRw, 2).Value = Me.ComboBox1.Value
   Cells(NewRw, 3).Value = Me.ComboBox2.Value
   Cells(NewRw, 4).Value = Me.TextBox3.Value
   Cells(NewRw, 5).Value = Me.TextBox4.Value
   Cells(NewRw, 6).Value = Me.ComboBox3.Value
   Cells(NewRw, 8).Value = Me.ComboBox4.Value
    Set ws1 = Nothing
End Sub

 

[PSkinny]

Hi Fluff
I have just tested your version 2 and it is exactly what the doctor ordered straightforward and concise, I probably tried to overthink it and wound up getting myself extremely confused (not hard to do). Thank you for taking the time to assist me.

 

[Fluff]

You're welcome & thanks for the feedback