Log file sorting with VBA

  • Thread starter Thread starter cphenley
  • Start date Start date
C

cphenley

I would like to sort a log file by each variable in the log. There is
date column and four other columns that I would like to be able to sor
alphabetically .

I would like to create macros that can sort each column.

For example - my column titles are date (A), item (B), action(C)
Reason(D), Person(E). I would like to create buttons with macros fo
each column, so that when I click a button it sorts all of the ro
entries.

I would also like to create a macro for a new entry which shifts all o
the rows down.

Does anyone know of any similar VBA code for either of these actions i
excel, or can anyone help me along?

Thanks in advance

Attachment filename: data-log.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=64561
 
Why new buttons for sorting? Have you removed the sort buttons from the
standard toolbar?

To insert a row below the active cell:

ActiveCell.Offset(1, 0).EntireRow.Insert

If you want the active cell to move down, too

ActiveCell.EntireRow.Insert
 
The new buttons are desirable because the program will be used by peopl
with almost no familiarity with excel. Having the buttons would greatl
help the acceptance of the program in the rural south.


Thank you for the help on entering a row
 
First, I'd lay out my worksheet this way:

Row 1. contains 5 buttons from the Forms toolbar (not the control toolbox
toolbar!)
Row 2. Contains the headers
And select A3 and use window|freeze Panes to make sure that both of these rows
are always visible.

Then your data in rows 3:xxx

Then put this one macro in a general module:

Option Explicit
Sub testme()

Dim BTN As Button
Dim LastRow As Long
Dim FirstRow As Long
Dim myCol As Long
Dim myOrder As Long

With ActiveSheet
Set BTN = .Buttons(Application.Caller)
myCol = BTN.TopLeftCell.Column
FirstRow = 3 'buttons in row 1, headers in row 2
LastRow = .Cells(.Rows.Count, myCol).End(xlUp).Row

If .Cells(FirstRow, myCol).Value > .Cells(LastRow, myCol).Value Then
myOrder = xlAscending
Else
myOrder = xlDescending
End If

.Range(.Cells(FirstRow, "a"), .Cells(.Rows.Count, "E")).Sort _
key1:=.Columns(myCol), _
header:=xlNo, order1:=myOrder
End With

End Sub

Make sure each button is assigned this macro.

And make sure each button is completely in the cell in its column.

The try clicking on any of the buttons a few times.
 
Dave,

You are an all star.

Thank you so much for your help.

It would have taken me weeks without your aide.

Conno
 
Back
Top