Sort on Double Click in All Sheets and Workbooks

hardeep.kanwar

Well-known Member
Joined
Aug 13, 2008
Messages
688
Hello Experts

Cross Posted
HTML:
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
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
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
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

Code:
Private Sub Workbook_Open()
Call InitializeApp
End Sub
Hope this helps ............
 

hardeep.kanwar

Well-known Member
Joined
Aug 13, 2008
Messages
688
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

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
    [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

Well-known Member
Joined
Aug 13, 2008
Messages
688
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
 

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
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

Well-known Member
Joined
Aug 13, 2008
Messages
688
I DELETE the Line "! you can paste your code to sort the sheet ....."

But Nothing Happen

In Class Module
Code:
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
Code:
Option Explicit
Public x As New Class1

Public Sub InitializeApp()
Set x.appbook = Application
End Sub
Double Click On "ThISWORKBOOK"

Code:
Option Explicit
Private Sub Workbook_Open()
Call InitializeApp
End Sub
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,944
Messages
5,508,290
Members
408,674
Latest member
PassSystems

This Week's Hot Topics

Top