Specify which row to insert values

[ipbr21054]

Morning,

I am using this working code below.

Currently when i run it the values are placed into Row 4
What i would like help / advice with please is as opposed to placing it in row 4 each time let the user specify the row for it to be inserted.

We have a grass cutting round so the customers are on a worksheet in round groups.
So just placing a new customer into row 4 isnt any use sames goes for sorting A-Z etc.

The user will look at the worksheet first & see that this new customer should be inserted into row 11
He would complete the form & then enter in TextBox9 the row number

The code should then create a new row & the values placed in this at row11
The existing value on the worksheet would then all move down 1 row.

Please can you assist.
Have a nice day.

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 6
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "CLONING TRANSFER SHEET"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
    
    With ThisWorkbook.Worksheets("CLONING")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 3
               .Cells(4, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
    
    Application.ScreenUpdating = False

    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    With ThisWorkbook.Worksheets("CLONING")
    .Range("A5").Select
    .Range("A4").Select
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    End With
End Sub

 

[ipbr21054]

Just an update as didnt get anywhere with my original request.

Here is my code at present of which send userform value to worksheet & inserts them into row 4

For some reason when i enter a post code example BS25 1AP into TextBox 4 i then transfer the values to the worksheet BUT as opposed to seeing BS25 1AP i actually see 0
I am not sure why BUT i think it has something to do with the code shown below in RED
I am not sure what that part of the code does anyway so now im stuck at present.

Maybe you could advise in basic so i can possibly figure it out.

Thanks

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 11
       With Me.Controls("TextBox" & i)
            If .Text = "" Then
            
                MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
    
    With ThisWorkbook.Worksheets("GRASS")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:K4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 3
               .Cells(4, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
    
    Application.ScreenUpdating = False

    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    With ThisWorkbook.Worksheets("GRASS")
    .Range("A5").Select
    .Range("A4").Select
    MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
    End With
    Unload GrassNewCustomer
End Sub

 

[ipbr21054]

This is now what i have but will need to stop as now confused on how to progress.

Upon pressing the command button the Input Box appears.
You enter a number for where the values are to be pasted.

This is where ive now come to & stuck.

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    Dim z As Integer
    z = Application.InputBox("INSERT DATA TO WHICH ROW ?", "ROW NUMBER MESSAGE BOX", Type:=1)
    For i = 1 To 11
       With Me.Controls("TextBox" & i)
            If .Text = "" Then
            
                MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
    
    With ThisWorkbook.Worksheets("GRASS")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:K4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 3
               .Cells(4, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
    
    Application.ScreenUpdating = False

    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    With ThisWorkbook.Worksheets("GRASS")
    .Range("A5").Select
    .Range("A4").Select
    MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
    End With
    Unload GrassNewCustomer
End Sub