vba to sort tables by clicking headers

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have two tables of data in a wksht, both with row and column headers. The column headers for both tables are located in Row9 so they align with each other on the sheet and each table is separated with some blank columns. The code below allows me to sort each table by double clicking either a row or column header and it works well. However, I'd like to introduce a macro (Macro12) into the second table only that executes just before the rows or columns sort on the double click of a header. What I have below works for the second table only, with Macro12 executed, when I double click a row header, however when any column headers are clicked in either table (because both tables have headers in Row9) macro 12 is executed - so by clicking a column header in the first table it executes macro 12 in the second table - how can I change the code so macro 12 only executes when column headers in the second table are clicked??
Any help much appreciated.

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
    Dim rng As Range
    Static sortAscending As Boolean
 
 
    With Target
        If (.Column = 5 And .Row = 9) Or (.Row <> 9 And .Column <> 5) Then
        Rem do nothing
       
        ElseIf .Row = 9 Then
       .CurrentRegion.Sort Key1:=.Cells, Order1:=2 + CLng(sortAscending),     Orientation:=xlSortColumns, Header:=True
        sortAscending = Not sortAscending
        Cancel = True
           
        ElseIf .Column = 5 Then
            Set rng = .CurrentRegion
            .CurrentRegion.Offset(0, 1).Sort _
                Key1:=.Cells, _
                Order1:=2 + CLng(sortAscending), _
                Orientation:=xlSortRows, _
                Header:=True
            sortAscending = Not sortAscending
            Cancel = True
           
        End If
        End With
       
       
      
        With Target
        If (.Column = 19 And .Row = 9) Or (.Row <> 9 And .Column <> 19) Then
        Rem do nothing
 
        ElseIf .Row = 9 Then
        [B]Call Macro12[/B]
        .CurrentRegion.Sort Key1:=.Cells, Order1:=2 + CLng(sortAscending),     Orientation:=xlSortColumns, Header:=True
            sortAscending = Not sortAscending
            Cancel = True
       
        ElseIf .Column = 19 Then
       [B] Call Macro12[/B]
            Set rng = .CurrentRegion
            .CurrentRegion.Offset(0, 1).Sort _
                Key1:=.Cells, _
                Order1:=2 + CLng(sortAscending), _
                Orientation:=xlSortRows, _
                Header:=True
            sortAscending = Not sortAscending
            Cancel = True
                      
        End If
        End With
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
Hello out there!
Anyone any ideas on this one??
Many thanks.
 

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
ok, have sussed it by moving the additonal section of 'Row = 9' code (that isolates the execution of macro 12) to the top of the main code and isolating it to the table in question, as follows:
ElseIf (.Row = 9 And .Column > 19 And .Column < 30) Then

Thanks to anyone who has spent any time looking at this for me.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,576
Messages
5,637,180
Members
416,960
Latest member
Carbon1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top