Insert row then sort A-Z

[ipbr21054]

Afternoon,

I have the code in use below.

All was working fine untill i added some code that once my values were sent from userform to worksheet then sort A-Z i get a RTE 439 see screen shot
The line in RED is shown in yellow when i debug it.

The value are sent from userform to worksheet all ok BUT then the sort doesnt happen.

Im sure ive missed something out but cant see it.

Private Sub TransferButton_Click()

Cancel = 0
If TextBox1.Text = "" Then
    Cancel = 1
    MsgBox "CUSTOMER'S NAME FIELD IS EMPTY", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    TextBox1.SetFocus
    
ElseIf TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "VIN FIELD IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    TextBox2.SetFocus

ElseIf ComboBox5.Text = "" Then
    Cancel = 1
    MsgBox "YEAR IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    ComboBox5.SetFocus
    
ElseIf ComboBox1.Text = "" Then
    Cancel = 1
    MsgBox "MY PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    ComboBox1.SetFocus
    
ElseIf ComboBox2.Text = "" Then
    Cancel = 1
    MsgBox "FORD PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    ComboBox2.SetFocus
    
ElseIf ComboBox3.Text = "" Then
    Cancel = 1
    MsgBox "ITEM IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    ComboBox3.SetFocus
    
ElseIf ComboBox4.Text = "" Then
    Cancel = 1
    MsgBox "TYPE IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    ComboBox4.SetFocus

End If

If Cancel = 1 Then
        Exit Sub
End If


Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B5:H5").Borders.LineStyle = xlContinuous
Range("B5:H5").Borders.Weight = xlThin
Range("B5:H5").Interior.ColorIndex = 6
Range("B5:H5").HorizontalAlignment = xlCenter
Sheets("RANGER").Range("B5").Select

Cancel = 0

If Cancel = 1 Then
Exit Sub
End If

Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim lastrow As Long
    
With ThisWorkbook.Worksheets("RANGER")
    .Range("B5").Value = TextBox1.Text
    .Range("D5").Value = TextBox2.Text
    .Range("C5").Value = ComboBox5.Text
    .Range("E5").Value = ComboBox1.Text
    .Range("F5").Value = ComboBox2.Text
    .Range("G5").Value = ComboBox3.Text
    .Range("H5").Value = ComboBox4.Text
            
    .Cells(5, 2).Resize
            

If .AutoFilterMode Then .AutoFilterMode = False
    x = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("B4:H" & x).Sort Key1:=.Range("B5"), Order1:=xlAscending, Header:=xlGuess
    .Range("B:B").Find(TextBox1.Value, , xlValues, xlWhole).Select
     Range("B5").Select
End With
        
    ActiveWorkbook.Save
        
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    MsgBox "DATABASE HAS BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
        


Unload RangerForm
Range("B4").Select
Range("B5").Select
End Sub

 

[ipbr21054]

Now sorted by removing that line shown in red