Insert row then sort A-Z

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,815
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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
 

Attachments

  • 1250.jpg
    1250.jpg
    31.9 KB · Views: 5

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,815
Office Version
  1. 2007
Platform
  1. Windows
Now sorted by removing that line shown in red
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top