RE WorkBook_ etc Events

[Ronaldj]

Jelen, Bill; Syrstad, Tracy. Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365) (Business Skills) (p. 115). Pearson Education. Kindle Edition.
TABLE 7-2 Workbook-level sheet and pivot table events
1) It lists Workbook_SheetBeforeDoubleClick, but it doesn't seem to work for me.
2) Workbook_SheetBeforeRightClick does work
3) "Setting Cancel to True prevents the default action from taking place."
What is the default action?
No description of argument types / or Sub declaration is given.

Workbook.SheetBeforeRightClick event (Excel)

Office VBA reference topic

learn.microsoft.com

Shows Workbook.SheetBeforeRightClick event (Excel)
??? : . (period)rather than _ (underscore)
Different context or ?

Confusing

 

[FormR]

Hi,

but it doesn't seem to work for me.

What exactly does "doesn't seem to work" mean? it does nothing? it throws an error? or something else? Where did you put your code, the workbook level events need to be in the "ThisWorkbook" module.

What is the default action?

The default action is what normally happens when you double click a cell, i.e. it enters edit mode.

 

[Micron]

Period and bang ( ! ) separators usually show that you are looking at a hierarchy. So Workbook is the object and what follows is a method or property. In this case I believe it's a method.
The default depends on the object being acted upon. Right click on a sheet tab and a sheet cell and you'll see 2 different results. Each is a default action.

 

[Micron]

In this case I believe it's a method.

That would be incorrect in the case of Excel. I don't believe I've ever seen the name of an event follow an object in Access vba, which is what I'm more used to. I'll have to take a look for my own curiosity.
Interesting that this even isn't even in the drop down list of workbook events, but it does work if I type it in. Not sure I can think of why I'd want to handle right click at the workbook level though.

 

[FormR]

Not sure I can think of why I'd want to handle right click at the workbook level

I see miss-read "RightClick" as "DoubleClick" - but @Ronaldj the "Default" behaviour is still just that, it's what happens by default when you right click a cell.

@Micron - surely the reason would be that you want the same event to occur for more than one worksheet in the workbook, without the need for repeating (and maintaining) the code more than once.

 

[Micron]

surely the reason would be that you want the same event to occur for more than one worksheet in the workbook, without the need for repeating (and maintaining) the code more than once.

I get that. Just never figured I would ever encounter a reason for disabling right click on every sheet in a wb. It's not as if Excel is all that good at security?

 

[RoryA]

It's not usually for security reasons, it's because you want to provide your own custom actions (Often a custom pop up menu).

 

[Ronaldj]

Thanks Micron and all
Yes, I want to Run a Loop that gives me Cursor Position. I terminate the Loop based on a value returned from GetKeyboardState
So, I believe I should Set Cancel to True as the first statement after the Sub Declaration i.e.
Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
...
(Only enter the Loop if the Sh and Target satisfy desired vales)
The Termination of the Loop results in a value being put into the Target Range
At this point in debug, once a RightClick has been done to a desired Target, that Target no longer will cause the Sub Workbook_SheetBeforeRightClick to be executed ? ? ?
Seems as if I am missing the proper way to Terminate the Loop.
I am in the process of making a simple example. What I have now is much too complicated: Many sheets, charts and data gathering.

 

[Micron]

Post 7 is marked as a solution. I can't see where this is solved yet, let alone that post being a solution.

 

[Ronaldj]

Ok. The Fog of War Prevails.
I would like to Post my .xlsm file that does what I wanted, but I don't understand how to do that.
I see the reference to XL2BB. I have read about it, but I don't see that it will show the VBA code.
I guess I can copy and paste using the VBA section wrapper, but it will be large and will not show the Worksheet.
Summary of my solution:
Use

Option Explicit
Const SheetAnalysesCol = 4
Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Application.EnableEvents = False
    If Sh.Name <> "Main" Then
        If (Target.Column = SheetAnalysesCol) Then
            Select Case Target.Row
                Case 2, 6, 10, 14
                    Get_Cursor_Pos ReturnTarget:=Target
                 Case Else
            End Select
        End If
    End If
    Application.EnableEvents = True
End Sub

in "This Workbook" - Code
Filter Sh and Range for the selected sheets and cells and then cause a Sub call to a GetCursorPositon loop
In the loop

Do While True
        dzoom = ActiveWindow.Zoom / 100
        pScaling = dpixelsize / dzoom
        GetCursorPos Hold
        If Hold.X_Pos < 0 Then Hold.X_Pos = Hold.X_Pos + 1920 ' Correct to Refernce Frame of the  Chart
        TabWorkSheet.Range("B1").Value = Hold.X_Pos
        TabWorkSheet.Range("B2").Value = Hold.Y_Pos
        If (GetAsyncKeyState(VK_Menu) <> 0) And (GetAsyncKeyState(VK_CONTROL) <> 0) Then
            If GetAsyncKeyState(VK_C) <> 0 Then
                Cells(ReturnTarget.Row, ReturnTarget.Column).Value = "Its C"
                Exit Do
            ElseIf GetAsyncKeyState(VK_L) <> 0 Then
                Cells(ReturnTarget.Row, ReturnTarget.Column).Value = "Its L"
                Exit Do
            ElseIf GetAsyncKeyState(VK_H) <> 0 Then
                Cells(ReturnTarget.Row, ReturnTarget.Column).Value = "Its H"
                Exit Do
            ElseIf GetAsyncKeyState(VK_O) <> 0 Then
                Cells(ReturnTarget.Row, ReturnTarget.Column).Value = "Its O"
                Exit Do
            ElseIf GetAsyncKeyState(VK_E) <> 0 Then
                Cells(ReturnTarget.Row, ReturnTarget.Column).Value = "End It"
                Exit Do
            End If
        End If
    Loop

Then keyboard:Ctrl-Alt C or L or H or O or E will break the loop and put a response into the cell that was Right-Clicked
I use the Ctrl-Alt keys to avoid conflicts with the System and Excel shortcut keys

Comments?