Sort when Worksheet_Change Does Nothing?!

[WpgExcel]

I have a user form set up to add a new part # to the worksheet, gather some data then create a new row adding that data once you click 'save' and that works fine but once the new row is added I want the sheet to automatically sort Ascending on column C...I've tried many different things and can't get it to work, below is the code I have now and it does nothing...


Private Sub Revised_Change(ByVal Target As Range)


Range("C1").CurrentRegion.Sort _
Key1:=Range("C1"), _
Order1:=xlAscending, _
Header:=xlYes


End Sub

Any suggestions are appreciated

 

[shg]

There is no such event as "Revised_Change" if you're expecting it to run automatically. Why not add the sort code to the code that inserts the row?

 

[WpgExcel]

There is no such event as "Revised_Change" if you're expecting it to run automatically. Why not add the sort code to the code that inserts the row?

Sorry, Worksheet_Change... doesn't work either, I was messing around with it and copied the wrong code. I've tried adding it to where the row is inserted but am getting nowhere, if you can tell me how that would be wonderful! That piece looks like...

Private Sub SAVE_Click()


'Part Num is mandatory
If Len(Trim(NewPartFrm.PrimeNum.Value)) = 0 Then
MsgBox "Part Number is Mandatory"
Exit Sub
End If


'Description is mandatory
If Len(Trim(NewPartFrm.PDesc.Value)) = 0 Then
MsgBox "Part Description is Mandatory"
Exit Sub
End If


'TypeCode is mandatory
If Len(Trim(NewPartFrm.TypeCode.Value)) = 0 Then
MsgBox "Part Number is Mandatory"
Exit Sub
End If


Dim emptyRow As Long


'Make Sheet2 active
Sheet2.Activate


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1


'Transfer information
Cells(emptyRow, 1).Value = PrimeNum.Value
Cells(emptyRow, 2).Value = PDesc.Value
Cells(emptyRow, 3).Value = TypeCode.Value
Cells(emptyRow, 5).Value = SubCode.Value
Cells(emptyRow, 7).Value = Mfr.Value
Cells(emptyRow, 8).Value = MfrNum.Value
Cells(emptyRow, 9).Value = UOMCombo.Value
Cells(emptyRow, 10).Value = MinQty.Value
Cells(emptyRow, 12).Value = PrefSup.Value
Cells(emptyRow, 13).Value = Cost.Value
Cells(emptyRow, 14).Value = Altsup.Value
Cells(emptyRow, 15).Value = Location.Value


Call UserForm_Initialize


End Sub

 

[shg]

Ther's no code to insert a row in what you posted.

 

[WpgExcel]

Ther's no code to insert a row in what you posted.

Maybe I'm using the wrong terminology sorry, it adds the data to the next blank row...I'm very new to this, learning via google as I go

 

[shg]

Private Sub SAVE_Click()
  Dim iRow          As Long

  'Part Num is mandatory
  If Len(Trim(NewPartFrm.PrimeNum.Value)) = 0 Then
    MsgBox "Part Number is Mandatory"

    'Description is mandatory
  ElseIf Len(Trim(NewPartFrm.PDesc.Value)) = 0 Then
    MsgBox "Part Description is Mandatory"

    'TypeCode is mandatory
  ElseIf Len(Trim(NewPartFrm.TypeCode.Value)) = 0 Then
    MsgBox "Part Number is Mandatory"

  Else
    With Sheet2
      iRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1
      .Cells(iRow, 1).Value = PrimeNum.Value
      .Cells(iRow, 2).Value = PDesc.Value
      .Cells(iRow, 3).Value = TypeCode.Value
      .Cells(iRow, 5).Value = SubCode.Value
      .Cells(iRow, 7).Value = Mfr.Value
      .Cells(iRow, 8).Value = MfrNum.Value
      .Cells(iRow, 9).Value = UOMCombo.Value
      .Cells(iRow, 10).Value = MinQty.Value
      .Cells(iRow, 12).Value = PrefSup.Value
      .Cells(iRow, 13).Value = Cost.Value
      .Cells(iRow, 14).Value = Altsup.Value
      .Cells(iRow, 15).Value = Location.Value
 [COLOR="#0000CD"]     .Range("C1").CurrentRegion.Sort Key1:=.Range("C1"), _
                                      Order1:=xlAscending, _
                                      Header:=xlYes[/COLOR]
    End With

    UserForm_Initialize
  End Sub

 

[shg]

Missed an End If:

Private Sub SAVE_Click()
  Dim iRow          As Long

  'Part Num is mandatory
  If Len(Trim(NewPartFrm.PrimeNum.Value)) = 0 Then
    MsgBox "Part Number is Mandatory"

    'Description is mandatory
  ElseIf Len(Trim(NewPartFrm.PDesc.Value)) = 0 Then
    MsgBox "Part Description is Mandatory"

    'TypeCode is mandatory
  ElseIf Len(Trim(NewPartFrm.TypeCode.Value)) = 0 Then
    MsgBox "Part Number is Mandatory"

  Else
    With Sheet2
      iRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1
      .Cells(iRow, 1).Value = PrimeNum.Value
      .Cells(iRow, 2).Value = PDesc.Value
      .Cells(iRow, 3).Value = TypeCode.Value
      .Cells(iRow, 5).Value = SubCode.Value
      .Cells(iRow, 7).Value = Mfr.Value
      .Cells(iRow, 8).Value = MfrNum.Value
      .Cells(iRow, 9).Value = UOMCombo.Value
      .Cells(iRow, 10).Value = MinQty.Value
      .Cells(iRow, 12).Value = PrefSup.Value
      .Cells(iRow, 13).Value = Cost.Value
      .Cells(iRow, 14).Value = Altsup.Value
      .Cells(iRow, 15).Value = Location.Value
      .Range("C1").CurrentRegion.Sort Key1:=.Range("C1"), _
                                      Order1:=xlAscending, _
                                      Header:=xlYes
    End With
    UserForm_Initialize
  End If
End Sub

 

[WpgExcel]

That added my data to row 2003 and gave me this error...

Run-time error '1004': Sort method of Range Class failed

 

[shg]

Is the sheet protected?

What's the address of [c1].currentregion?

 

[WpgExcel]

No, its not protected and sorry, but I don't know what you mean by 'the address'?