Auto_open in an .xla

[Weaver]

We have a custom sort routine activated by a double click

Sub Auto_Open()
    On Error Resume Next
    Application.OnDoubleClick = "AutoSort"
    
End Sub

It used to sit in a module in each workbook, but I modified it to work from personal.xls. When I moved it to a .xla addin file it stopped, so I'm figuring that opening a .xls doesn't trigger the auto_open event. Is the case? If so, is there another way, if not, any idea what I'm doing wrong?

Cheers,

 

[xenou]

Not sure but maybe try using a Workbook_Open Event ... should do the same and is the newer technology for Excel VBA ... if that doesn't work we'll really start scratching our heads.

Alex.

 

[ZVI]

Hi,
Try this:

<font face=Courier New>
' Code should be in ThisWorkbook (class) module of the personal.xls or XLA
Dim WithEvents Apps As Application

Private Sub Workbook_Open()
  Set Apps = Application
  ' Redefine double click event to AutoSort() subroutine of standard module
  Apps.OnDoubleClick = "'" & ThisWorkbook.Name & "'!AutoSort"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Set Apps = Nothing
End Sub</FONT>

Vladimir

 

[ZVI]

Without hidden OnDoubleClick member approx the same (but not exactly the same) result could be achieved:

<font face=Courier New>
' Code should be in ThisWorkbook (class) module of the personal.xls or XLA
Dim WithEvents Apps As Application

Private Sub Apps_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  ' Put your code for DoubleClick event
  MsgBox "Your sorting..."
End Sub

Private Sub Workbook_Open()
  Set Apps = Application
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Set Apps = Nothing
End Sub</FONT>

 

[Weaver]

Looks promising - will give this a try later today if possible. It's a work thing, but something of a side project!

Cheers for the replies.

 

[Ivan F Moala]

If you do use Workbook open event of the Addin, which should work, then also put in the reset code to disable it in the Addinuninstall event other wise the double click event will always call this addin.

 

[ZVI]

Not sure that AddInUninstall event is required if BeforeClose event is used. Uninstalling of AddIn means the unloading which causes BeforeClose event where resetting code could be.

But agree that using of hidden OnDoubleClick event requires the correct resetting of this application event, here is the fixed code:

<font face=Courier New>
' Code should be in ThisWorkbook (class) module
Private Sub Workbook_Open()
  ' Redefine double click event to AutoSort() subroutine of standard module
  Application.OnDoubleClick = "'" & ThisWorkbook.Name & "'!AutoSort"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Application.OnDoubleClick = ""
End Sub</FONT>

Please take into account that usage of hidden VBA members is not recommended.

Vladimir

 

[Weaver]

We used the code in reply #4, which seems to work (after I changed all the 'activesheet' references to 'sh'. Do any of these new provisos apply in that situation? I'm assuming not, but I'd appreciate confirmation.

Thanks for your help so far!

 

[ZVI]

I confirm that all is correct in code of post #4.
You can also use variable Sh for choosing the exact sheets where your sorting will play.
Cheers,
Vladimir

 

[Weaver]

For info only, but I noticed the link to the code seems to 'break' when you edit it. Obviously this isn't a problem.

All is looking good now, Cheers!