Worksheet_BeforeRightClick() with ahk? Topic is solved

[cgx5871]

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

Code: Select all

~LButton::{
    if (A_PriorHotkey != "~LButton" or A_TimeSincePriorHotkey > 400)
    {	
        KeyWait "LButton"
        return
    }	
	Cancel:=true
}

[boiler]

See this Excel event handler.

[cgx5871]

See this Excel event handler.

class Workbook_Events
Is there a V2 version?

[boiler]

I don’t know of one, but I wouldn’t think it would be that hard to convert.

[flyingDman]

See here for a Worksheet.Change event : viewtopic.php?f=82&t=114601&p=510329#p510314

I haven't had much luck with a Worksheet.BeforeRightClick event.

[Datapoint]

Code: Select all

#Requires AutoHotkey v2.0
Persistent
; Create an instance of Excel
xlApp := ComObject("Excel.Application")
; Make xlApp visible
xlApp.Visible := true
; Add a new workbook
MyWb := xlApp.Workbooks.Add()
; Create a new instance of Workbook_Events and connect MyWb to it.
ComObjConnect(MyWb, Workbook_Events() )

class Workbook_Events
{
    ; Note: The docs indicate that "Cancel" is the third parameter, but it seems to be the first.
    ; https://learn.microsoft.com/en-us/office/vba/api/Excel.Workbook.SheetBeforeRightClick
    ; Event SheetBeforeRightClick(Sh As Object, Target As Range, Cancel As Boolean)
	SheetBeforeRightClick(&Cancel, Target, Sh, wkb) {
        ; Cancel = False when the event occurs. If the event procedure sets this argument to True, the default right-
        ; click action isn't performed when the procedure is finished.
        Cancel := -1

        MsgBox "Address> " Target.Address "`nSh.Name> " Sh.Name "`nwkb.Name> " wkb.Name
	}

}

[flyingDman]

@DataPoint
Nice catch !! It did not occur to me to change the order....
Using the reference above, and worksheet_beforerightclick rather than workbook_sheetbeforerightclick the following simplifies it:

Code: Select all

Persistent
XLSht := ComObjActive("Excel.Application").ActiveSheet
ComObjConnect(XLSht, "Sht_")

Sht_BeforeRightClick(&cancel, *)
	{
	cancel := true
	}

[Datapoint]

Cool. Both work, it just depends on whether the events you are monitoring are connected to a workbook or a worksheet object. For future forum readers: you would use the corresponding method depending on the type of object connected - workbook vs worksheet.

Nice catch !! It did not occur to me to change the order....

Yeah, I was pretty confused at first. Turns out the docs are wrong?
I Implemented the __Call meta function that boiler linked to. (and similar to the __Call function I posted in another thread for v1 Outlook Events)
If you comment out the SheetBeforeRightClick method the order of the parameters becomes clear.

Code: Select all

#Requires AutoHotkey v2.0
Persistent
; Create an instance of Excel
xlApp := ComObject("Excel.Application")
; Make xlApp visible
xlApp.Visible := true
; Add a new workbook
MyWb := xlApp.Workbooks.Add()
; Create a new instace of Workbook_Events and connect MyWb to it.
ComObjConnect(MyWb, Workbook_Events() )

class Workbook_Events
{
	__Call(Name, Params) {
		EventInfo := ""
		for i, val in Params
		{
			if !ComObjType(val)
				EventInfo .= "Arg#: "       i
						   . "`nNot an object`n`n"
			else
				EventInfo .= "Arg#: "       i
						   . "`nVarType: "  ComObjType(val)
						   . "`nName: "     ComObjType(val, "Name")
						   . "`nIID: "      ComObjType(val, "IID")
						   . "`nClass: "    ComObjType(val, "Class")
						   . "`nCLSID: "    ComObjType(val, "CLSID") "`n`n"
		}
		MsgBox "Event: " Name "`n`nParams:`n`n" EventInfo, Name
	}

	; Event SheetBeforeRightClick(Sh As Object, Target As Range, Cancel As Boolean)
	; Note: The docs seems to indicate that "Cancel" is the third parameter, but it seems to be the first.
	; https://learn.microsoft.com/en-us/office/vba/api/Excel.Workbook.SheetBeforeRightClick
	SheetBeforeRightClick(&Cancel, Target, Sh, wkb) {
		; Canacel = False when the event occurs. If the event procedure sets this argument to True, the default right-
		; click action isn't performed when the procedure is finished.
		Cancel := -1
		MsgBox "Address> " Target.Address "`nSh.Name> " Sh.Name "`nwkb.Name> " wkb.Name, "SheetBeforeRightClick"
	}
}

[flyingDman]

You can always use cell.parent.name and cell.parent.parent.name to get the name of the sheet and the name of workbook:

Code: Select all

Persistent
XLSht := ComObjActive("Excel.Application").ActiveSheet
ComObjConnect(XLSht, "Sht_")

Sht_BeforeRightClick(&cancel, cell, *)
	{
	cancel := true
	msgbox cell.parent.name "`n" cell.parent.parent.name "`n" cell.value "`n" cell.address(0,0)
	}

Possible application of this:

Spoiler

Code: Select all

Persistent
XLSht := ComObjActive("Excel.Application").ActiveSheet
ComObjConnect(XLSht, "Sht_")

Sht_BeforeRightClick(&cancel, cell, *)
	{
	cancel := true
	cMenu := Menu()
	for x,y in ["Apple","Orange","Banana"]
		cMenu.Add(y, cMenuHandler)
	cMenu.Show()
	cMenuHandler(ItemName,*) => cell.value := ItemName
	}

[cgx5871]

You can always use cell.parent.name and cell.parent.parent.name to get the name of the sheet and the name of workbook:

Code: Select all

Persistent
XLSht := ComObjActive("Excel.Application").ActiveSheet
ComObjConnect(XLSht, "Sht_")

Sht_BeforeRightClick(&cancel, cell, *)
	{
	cancel := true
	msgbox cell.parent.name "`n" cell.parent.parent.name "`n" cell.value "`n" cell.address(0,0)
	}

Possible application of this:

Spoiler

Code: Select all

Persistent
XLSht := ComObjActive("Excel.Application").ActiveSheet
ComObjConnect(XLSht, "Sht_")

Sht_BeforeRightClick(&cancel, cell, *)
	{
	cancel := true
	cMenu := Menu()
	for x,y in ["Apple","Orange","Banana"]
		cMenu.Add(y, cMenuHandler)
	cMenu.Show()
	cMenuHandler(ItemName,*) => cell.value := ItemName
	}

how to ComObjConnect existing workbook (like test.xls). and apply toggle every ActiveSheet

Code: Select all

MyWb := xlApp.ActiveWorkbook

Error: Parameter #1 of ComObjConnect requires a ComValue, but received an empty string.

006: xlApp.Visible := true
009: MyWb := xlApp.ActiveWorkbook
▶ 013: ComObjConnect(MyWb, Workbook_Events() )
015: Workbook_Events
015: {
=======================

MyWb := xlApp.Workbooks("test.xlsm")
Error: (0x8002000B)
Specifically: Workbooks

[flyingDman]

This works for me:

Code: Select all

Persistent

Xl := ComObject("Excel.Application")
XLWb := Xl.Workbooks.Open("C:\Users\xxx\Scripts\Test.xlsx")
Xl.visible := true

;~ or    XLWb := ComObjActive("Excel.Application").ActiveWorkBook
ComObjConnect(XLWb, "Wb_")

Wb_SheetBeforeRightClick(&cancel, cell, sheet, wkb, *)
	{
	cancel := true
	lst .= cell.address(0,0) "`n"
	lst .= sheet.name "`n"
	lst .= wkb.name
	msgbox lst
	}