adding code to sort a list in VBA

T

taniedzw

I have a user form that enters data to a file and the file should be
sorted in date order.

How can I add code to an already existing code in Visual Basic so that
after the user clicks the "OK" button the data gets entered, then after
that it automatically sorts the data according to the date column.

I tried recording a macro to do it and then copy/paste it to the code
but i get an error.

Any ideas as to what the code should look like, where it should be
placed in the program?

Thanks
Tim
 
T

taniedzw

This is what I was trying before. Basically I inserted a picture
assigned a macro to it and recorded this macro. I want to have it sor
my list (columns A thru O) according to the date (column B). I woul
like to do this after the user enters the data from the form, but
want it to be automatic so that you don't have to push a button to d
it.

This is the code I was trying for the sort function, below that is th
code for my user form. I would like to incorporate the sort code int
the user form code.

Thank you for your assistance, it is very much appreciated


---------

Private Sub cmdSort_Click()

Columns("B:B").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.SmallScroll Down:=216
ActiveSheet.Shapes("Rectangle 3").Select
Range("D229").Select

End Sub

---------

Private Sub cmdCancel_Click()

Unload Me

End Sub

Private Sub cmdClear_Form_Click()

Call UserForm_Initialize

End Sub

Private Sub cmdOK_Click()

ActiveWorkbook.Sheets("Data Log").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtTag.Value
ActiveCell.Offset(0, 1) = txtDay.Value
ActiveCell.Offset(0, 2) = cboMonth.Value
ActiveCell.Offset(0, 12) = cboVendor.Value
ActiveCell.Offset(0, 3) = txtHours_Lost.Value
ActiveCell.Offset(0, 4) = txtPart_Number.Value
ActiveCell.Offset(0, 5) = txtSerial_Number.Value
ActiveCell.Offset(0, 6) = txtQTY.Value
ActiveCell.Offset(0, 7) = txtDescription.Value
ActiveCell.Offset(0, 8) = txtProblem.Value
ActiveCell.Offset(0, 9) = txtReference_Name.Value
ActiveCell.Offset(0, 10) = cboProblem_Type.Value
ActiveCell.Offset(0, 11) = cboSolution.Value
If optYes = True Then
ActiveCell.Offset(0, 13).Value = "Yes"
Else
ActiveCell.Offset(0, 13).Value = "No"
End If
If optLost = True Then
ActiveCell.Offset(0, 14).Value = "Yes"
Else
ActiveCell.Offset(0, 14).Value = "No"
End If
Range("A1").Select

End Sub

Private Sub UserForm_Initialize()
txtTag.Value = ""
txtHours_Lost.Value = ""
txtQTY.Value = ""
txtPart_Number.Value = ""
txtSerial_Number.Value = ""
txtDescription.Value = ""
txtProblem.Value = ""
txtReference_Name.Value = ""
With cboProblem_Type
.AddItem "Boom Problem"
.AddItem "Electrical"
.AddItem "Fit"
.AddItem "Flawed Before Arrival"
.AddItem "In-House Damage"
.AddItem "Paint Defect"
.AddItem "Part Malfunction"
.AddItem "Shipping Error"
.AddItem "Update Prints"
End With
cboProblem_Type.Value = ""
txtDay.Value = ""
With cboMonth
.AddItem "Jan-04"
.AddItem "Feb-04"
.AddItem "Mar-04"
.AddItem "Apr-04"
.AddItem "May-04"
.AddItem "Jun-04"
.AddItem "Jul-04"
.AddItem "Aug-04"
.AddItem "Sep-04"
.AddItem "Oct-04"
.AddItem "Nov-04"
.AddItem "Dec-04"
End With
cboMonth.Value = ""
With cboVendor
.AddItem "Ardco"
.AddItem "Badger Truck"
.AddItem "Bent Tubes"
.AddItem "Carraro"
.AddItem "CAT"
.AddItem "Cylinder Shop"
.AddItem "D.B."
.AddItem "Eaton/Vickers Corp."
.AddItem "Galaxy"
.AddItem "Gilmore"
.AddItem "Hobbs Division"
.AddItem "IAE Power Products"
.AddItem "Koski Welding"
.AddItem "Massie"
.AddItem "Muffler Shop"
.AddItem "Parker"
.AddItem "Parker Hannifin Corp."
.AddItem "Peer"
.AddItem "Price Engineering"
.AddItem "Seats Inc."
.AddItem "Selkey"
.AddItem "Sterling Technoligies"
.AddItem "Teleflex/Morse"
.AddItem "The Nason Company"
.AddItem "UPS"
.AddItem "Value Added"
.AddItem "Van Straten"
End With
cboVendor.Value = ""
With cboSolution
.AddItem "Better QC at Vendor"
.AddItem "Fixed In-House"
End With
cboSolution.Value = ""
txtVendor = ""
txtTag.SetFocus

End Su
 
A

AlfD

Hi!

The line
Columns("B:B").Select
Click to expand...
will probably not produce what you want. This is an instruction t
select all of the cells in col B which you then go on to sort.

Better just range("B2").select and leave it to Excel to work out wha
the whole database you are sorting is. (I'm assuming you do want th
entries in other columns to follow B when the sort happens).

Since Excel can be less than dependable on selecting the right sor
target area, best would be to define it.

Al
 
Top