Sorting Data by clicking the header

**jkurtz** · 08-10-2012, 02:16 PM

I'm currently developing a spread sheet in Excel 2007 of general plant information for my office. We have some not so computer friendly people in the office so we wish to make this as simple as possible. The ideal situation would be to set it up so that you could just click on any header such as; Scientific Name, Common Name, Native location etc. and all of the data would be sorted by that header in alphabetical order.

I have it set up so that you go to Data -> Sort A-Z button, but they want it simpler.

I have also formatted it so that there are the pull down windows on each column that you click and then select how you would like to order the information.

They want it simpler.

I have been researching this and think that Macros are the answer. However, whenever I try to run one this message pops up:Macro Error.jpg

I have selected Enable All Macros and checked 'Trust access to the VBA project object model'. That is as far as the internet has been able to take me so far.

**tigeravatar** · 08-10-2012, 03:17 PM

jkurtz,

Welcome to the forum!
Attached is an example workbook based on the criteria you described.
It uses the worksheet double-click event to sort the data by that double-clicked column header ascending.

In the Sheet1 code module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Not Intersect(Target, Me.Range("A1:D1")) Is Nothing Then
        Target.CurrentRegion.Sort Target, xlAscending, Header:=xlYes
        Cancel = True
    End If
    
End Sub

Also, from a security standpoint, you should never trust access to the VBA project object model.

**jkurtz** · 08-10-2012, 03:46 PM

tigeravatar,

Thank you for the welcome to the forum!

And thank you so much for the help! That is exactly what I'm looking for to a T! You are amazing!

jkurtz