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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,643
Messages
5,660,057
Members
418,546
Latest member
antrixx

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