Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier Sorting
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 -- anar_bak ----------------------------------------------------------------------- anar_baku's Profile: http://www.excelforum.com/member.php...fo&userid=1825 View this thread: http://www.excelforum.com/showthread.php?threadid=38746 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier Sorting
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier Sorting
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 wrote: 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 the column of the header that you want it sorted by (for eg. date) and then go to Date Sort. I want to make it easier for the users of this database to sort by each header. For each header I've recorded a Macro that is preset to how it should sort. All I need now is make each header clickable so that when a user clicks on it an associated Macro is run. Can anyone help me please. I tried Insert Hyperlink, but couldn't link it from there. Or maybe there is an even easier way to achieve the same result that I don't know of. Any help will be much appreciated. Many thanks, Anar |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier Sorting
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 -- anar_bak ----------------------------------------------------------------------- anar_baku's Profile: http://www.excelforum.com/member.php...fo&userid=1825 View this thread: http://www.excelforum.com/showthread.php?threadid=38746 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier Sorting
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. anar_baku wrote: Thanks for your responses guys. Sorry Aidey, but yours wasn't very helpful, since it ignored my original posting and the word "Easier" in my subject. Gareth's was an interesting one; I might use it in the future, but I've already found a solution, although not an ideal one. Since I couldn't assign macros to cell text I simply made up text boxes (roughly the same size as the respective cells) and assigned the macros to them. Visibly it's all the same, although not the ideal solution from a programmer's point of view! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
An easier way? | Excel Discussion (Misc queries) | |||
Is there an easier way? | Excel Worksheet Functions | |||
easier way to do this? | Excel Discussion (Misc queries) | |||
got to be an easier way? | Excel Discussion (Misc queries) | |||
Is there an easier way? | Excel Discussion (Misc queries) |