0

i'd like to do the following: I want to be able to modify the BeforeDoubleClick Event in my personal.xlsb. Here is an example for what I have in mind: I want, that when I doubleclick a cell, its backgroundcolour is set to green. Usually I would have to do this in the Workbooks Workbook_BeforeDoubleClick Event, however I want to be able to do that in any workbook and from my personal.xlsb, so that I can assign different methods to the BeforeRightClick Event. Ideally I would have a userform with a few buttons, each one assigning a different method to the BeforeRightClick Event.

Is this possible? I was not able to find a solution online.

Best regards!

earthling
  • 620
  • 6
  • 20

2 Answers2

1

It is possible - to do this you need to capture Application events.

A full explanation with sample code is available here: http://www.cpearson.com/excel/AppEvent.aspx

Olly
  • 7,749
  • 1
  • 19
  • 38
1

It is possible with a few lines of code in the ThisWorkbook module of your Personal file:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Debug.Print "RightClick on Sheet: " & Sh.Name & " at " & Target.Address
End Sub

(note that after adding the code, you have to save, close Excel completely and restart it so that the Workbook_Open event is fired).

However, there are two things to consider:

  1. When you set Cancel = True, you steal all the context menus - I would not recommend to do so. However, if you set Cancel = False, there will be two actions in parallel, first your action, and only after that action is finished (which may be a long time when you show a form), the context menu will pop up - very confusing.
  2. There are several cases where global Vars are reset, see discussion here: What is the lifetime of a global variable in excel vba? Once one of these events happens, your app variable is reset and from that point on the events will no longer fire until you restart Excel.

My suggestion: Assign a Keyboard Shortcut to a Macro in your personal workbook. Instead of Right Click, the event to fire would be for example Ctrl+Shift+F to Format your worksheet.

FunThomas
  • 23,043
  • 3
  • 18
  • 34