Solved: Sort a column

[Anne Troy]

I want to click on, for instance, cell A2, and have it sort my data from rows 3 and down by column A data.

I want to click on B2, and have it sort my data from rows 3 and down by column B...etc.

Do I *have* to make it a double-click?

Anybody want to write me some code? The file will end up as a free download and is a "To-Do List".

[mvidas]

Hi Anne,

Put this in the sheet object that you want sorted:[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row <> 2 Then Exit Sub
Rows("3:65536").Sort Key1:=Cells(3, Target.Column), Order1:=xlAscending, Header:=xlNo
End Sub[/vba]
Matt

[Anne Troy]

You're a DREAM, Matt. Will try when I get back from my Dr. appointment.

Want another quickie?
I know how to make a workbook open to a specific spot. How could I make the workbook open to a different specific spot (instructions worksheet) ONLY the first time the workbook is opened?

[mvidas]

Sure thing.

When the workbook is open, go to File, then Properties. Go to the 'Custom' tab, under name put 'BeenOpened', under type keep it 'Text', and under value put "False" (or anything except "True", actually). Then click add, then ok to close the properties window. Next in the ThisWorkbook object, put:[vba]Private Sub Workbook_Open()
If ThisWorkbook.CustomDocumentProperties("BeenOpened") <> "True" Then
Sheets("instructions").Select
ThisWorkbook.CustomDocumentProperties("BeenOpened") = "True"
End If
End Sub[/vba]Should take care of it!

[Anne Troy]

OMG, Matt!! That works for ALL columns!! That's TOO freaking cool.

[Anne Troy]

OMG. Thanks so much!! Too cooool.

[Zack Barresse]

Sounds similar to another question I saw answered the other day. Solution originally worked by Dave Paterson ..

Put a button on the worksheet and assign it this code:

[VBA]Option Explicit

Sub testme()

Dim myRng As Range
Dim myCol As Long

myCol = ActiveCell.Column

With ActiveSheet
Set myRng = .Range(.Cells(1, myCol), _
.Cells(.Rows.Count, myCol).End(xlUp))
End With

myRng.Sort key1:=myRng.Cells(1), order1:=xlAscending, header:=xlYes

End Sub[/VBA]

I guessed on the starting row (1) and whether you had headers (I guessed Yes). But you could change that to what you want.

If you're new to macros, you may want to read David McRitchie's intro at http://www.mvps.org/dmcritchie/excel/getstarted.htm.



************************************************



As another solution, you could put a clear box on top of each cell of each header, take out any fill color or fill lines. Assign this macro to them ...

[vba]Option Explicit

Sub AutoFilterCurrentColumnPlease()

Dim rngStart As Range, rngEnd As Range, rngMix As Range
Dim s As Shape

Set s = ActiveSheet.Shapes(Application.Caller)
Set rngStart = Range(s.TopLeftCell.Address)
Set rngEnd = Cells(Rows.Count, rngStart.Column).End(xlUp)
Set rngMix = Range(rngStart, rngEnd)

rngMix.Sort key1:=rngMix(2), header:=xlYes

End Sub[/vba]

This will work for any column. If you'd like a test workbook, let me know.

Just make sure the shape is contained (especially the top left) inside or equal to the cell size desiring clicking on to run routine. Also, this will only sort the column clicked, not any other column.

[mvidas]

If you're new to macros, you may want to read David McRitchie's intro at http://www.mvps.org/dmcritchie/excel/getstarted.htm

I'm guessing you didn't see who posted this Q?

[Anne Troy]

LOL My thought exactly, Matt.

[Zack Barresse]

Yeah, yeah.. so I'm curious, did you try the second solution?? Seemed to be exactly what you were asking for.. The only thing it's really got over Matt's is the fact that it's not running everytime you click a cell.

[Anne Troy]

Nope. I didn't. Matt's is working very well.. ain't broke, don't fix it, and all that. But, I'm gonna go ahead and upload the workbook here. This is all little stuff! But... stuff I can't do.

Here's what we've got. A userform to print reports. While I know we probably could've done this with messageboxes, I hate 'em for the "average" user. I think the userform is self-explanatory EXCEPT that whenever we print from this workbook, we do not want to print Row 1 (that big ugly black background).

The Archive button should take any records off of the MyToDo worksheet and move them to the Archive worksheet. The records it should remove are those that match, in the Status column, the values from B3 or B4 of the Statuses column on the DataLists worksheet.

The About button should bring up frmAbout. If you contribute any code, please add your name!

[acw]

Hi Dreamboat

A couple of bits for you.


Tony

[Anne Troy]

You know, I figured I didn't get the notif 'cause I didn't come back to read somebody's post, but I see you're the only one who posted after I did. Why didn't I get that notif? This makes me crazy.

Thanks!! I'm trying it out!

[Anne Troy]

I just now received your notif, acw. Hmph! Anyway, works GREAT!! Thanks! Just need the Print now.

[acw]

Anne

Some code for you

[VBA]
Sub PrintMTD()
'AUTHOR: A.C. Ward
'DATE: 31/10/05
Sheets("MyToDo").PageSetup.PrintArea = "$B$2:$I$" & Sheets("MyToDo").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("MyToDo").PrintOut
End Sub


Sub PrintArchive()
'AUTHOR: A.C. Ward
'DATE: 31/10/05
Sheets("Archive").PageSetup.PrintArea = "$B$2:$I$" & Sheets("Archive").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Archive").PrintOut
End Sub
[/VBA]

I'll be mean and make you like the relevant buttons to the correct code block.


Tony

[Anne Troy]

Thank you thank you thank you!! I'll check it out no later than tomorrow!