Easier Sorting

[anar_baku]

Hi Guys,

I have a table on Excel with different headers at the top row (i.e
Date, Name, etc). You can usually sort the table by selecting th
column of the header that you want it sorted by (for eg. date) and the
go to Date > Sort. I want to make it easier for the users of thi
database to sort by each header. For each header I've recorded a Macr
that is preset to how it should sort. All I need now is make eac
header clickable so that when a user clicks on it an associated Macr
is run. Can anyone help me please. I tried Insert > Hyperlink, bu
couldn't link it from there.

Or maybe there is an even easier way to achieve the same result that
don't know of. Any help will be much appreciated.

Many thanks,

Ana

 

[aidey]

Why not just have your users do this: -
1. Select the header to sort on
2. Press the Sort Ascending or Sort Descending button (on the standard
toolbar, just to the right of the chart wizard)

Aidey

 

[Gareth]

I use something that let's you just doubleclick on the top row of every
column to sort it. This method allows you to weasily to sort by, say,
first Column, A, B, C by doubleclicking on C1, B1 and then A1.

It's also very useful (I think) because it lets users sort a sheet that
is protected - should you wish to prevent them from changing some (or
all) the data.

Place the below code in the code sheet of the worksheet you wish to sort
on.

Private SortColumn As Integer

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.EnableEvents = False
If Target.Row = 1 Then
SortLog Target.Column
End If
Application.EnableEvents = True
Cancel = True
End Sub

Private Function SortLog(col As Integer) as boolean
Dim rng As Range
Dim myOrder As Integer

If col = SortColumn Then
myOrder = xlDescending
SortColumn = 0
Else
myOrder = xlAscending
SortColumn = col
End If
Set rng = Me.UsedRange

''You may like to include this lines to handle sheet protection
'- we assume the sheet is
'''Me.Protect Password:="XXXX", UserInterfaceOnly:=True

Me.Cells.Sort Key1:=Me.Range(Cells(1, 2).Address), _
Key2:=Me.Range(Cells(1, col).Address), _
Order1:=myOrder, Header:=xlYes

End Function

 

[anar_baku]

Thanks for your responses guys. Sorry Aidey, but yours wasn't ver
helpful, since it ignored my original posting and the word "Easier" i
my subject.

Gareth's was an interesting one; I might use it in the future, but I'v
already found a solution, although not an ideal one. Since I couldn'
assign macros to cell text I simply made up text boxes (roughly th
same size as the respective cells) and assigned the macros to them
Visibly it's all the same, although not the ideal solution from
programmer's point of view

 

[Gareth]

I'm glad you found a solution - sounds like a good workaround.

Word of warning (I'm sure you're already aware), in case someone resizes
a column (obviously they can't if you protect the sheet) you should set
the textbox properties to Move and Resize with cells.

Obviously, if you need to add a new column, you'll have to add a new
textbox. If you use my solution (which has its disadvantages too) it
works off the usedrange - so it requires zero upkeep should you add or
remove columns.