Sort on Double Click in All Sheets and Workbooks

[hardeep.kanwar]

Hello Experts

Cross Posted

http://www.vbaexpress.com/forum/showthread.php?t=33437

Is it Possible to Sort the Data upon Double Click

Means if i Double Click on A1 Header it Sort the Whole Data By column A

If i Double Click on B1 it Sort by column B, If Double Click on G1 it Sort the Data by Column G

whenever I Double Click on any Column It Sort by that Column

I got Some good codes from VBAXPRESS.COM

But, It works in only particular workbook or Sheet

I need to Work in every workbook or Worksheet, Same as ADD-IN or Macros Works

Or how can i Enter this Code in My PERSONAL.XLSX, SO that can it works on every Worksheets and Workbooks

Regards

Hardeep

 

[hsk]

You probably need to insert a Class Module ... Try this

In your Addin (or the PERSONAL.xls .. whatever u choose)

Go to VBA Editor - Right click the VBAProject and Insert Class Module.
It will insert a new class - say Class1. Open the Class1 and paste following code

Public WithEvents appbook As Application
Private Sub appbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    MsgBox "Double Clicked"
    Cancel = True
    ! you can paste your code to sort the sheet .....
End Sub

Then Insert a module and paste the following code

Public x As New Class1

Public Sub InitializeApp()
Set x.appbook = Application
End Sub

Double click Thisworkbook and call InitializeApp on Workbook.Open Event

Private Sub Workbook_Open()
Call InitializeApp
End Sub

Hope this helps ............

 

[hardeep.kanwar]

Thanks HSK

But, Unfortunately its Not Working


After your Instruction, when i Click on Header Say in A1

It show the Msg Box "DOUBLE CLICKED", Then i Click Ok

But , Nothing Happen

When i Paste the First Code i.e.i in Class Module

Public WithEvents appbook As Application
Private Sub appbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    MsgBox "Double Clicked"
    Cancel = True
    [COLOR="Red"]! you can paste your code to sort the sheet .....[/COLOR]
End Sub

It show in Red Color, then i Delete this Line

But, Again its Not working

Regards

 

[hardeep.kanwar]

Secondly, After Double Click on "THISWORKBOOK" I found 2 box 1 is "Workbook" and another one is "OPEN"

I couldn't found InitializeApp on Workbook.Open Event


Is it Same or Differ from Each Other

 

[hardeep.kanwar]

Bump!

 

[hsk]

If you are getting Msgbox on double clicking ... that means ur macro is getting triggered correctly .....
Now only thing u need is to replace this line
"! you can paste your code to sort the sheet ....."

with your code which is sorting the sheet .... you wud need to change few things though to make it generic ...

try it ... if not working paste code here ...

 

[hardeep.kanwar]

I DELETE the Line "! you can paste your code to sort the sheet ....."

But Nothing Happen

In Class Module

Option Explicit

Public WithEvents appbook As Application
Private Sub appbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    MsgBox "Double Clicked"
    Cancel = True
   
End Sub

In Module

Option Explicit
Public x As New Class1

Public Sub InitializeApp()
Set x.appbook = Application
End Sub

Double Click On "ThISWORKBOOK"

Option Explicit
Private Sub Workbook_Open()
Call InitializeApp
End Sub