Trigger VBA on action 'increase indent'

[msdejong79]

Hello all,

Modifying the indent of a cell is not picked up by excel as a change, yet I would like to have excel execute a macro when I increase or decrease the indent of a cell.

The furthest I came was with this post:

http://www.mrexcel.com/forum/excel-questions/448695-increase-indent-click-event-using-withevent.html

However, the solution provided here was for excel 2003. I am using 2010. Does anyone have an idea how I could trigger VBA upon changing the indent?

Thanks a bunch in advance!

 

[Jaafar Tribak]

Hi snb_

Your version of the code takes away the need to iterate through all commandbars and does work but it is language-dependent .. Unlike the original code,It will only work with the English version of MSOffice not with other languages as well like french that I am using ... The idea was to write a more generic script that works regardless of the UI language

Regards

 

[snb_]

In that case I'd prefer:

     With Application.CommandBars
       If lResult = 0 And InStr(.FindControl(, 3161).Caption & .FindControl(, 3162).Caption, oIA.accName(&H0&)) Then ActiveSheet.Calculate
     End With

 

[Jaafar Tribak]

Thanks snb_ That is much simpler

To wrap this up, here is a more generic code that should hook all the old-style commandbarbuttons .. The code must first check that the mouse click is within the Ribbon window area via the AccParent Property

Option Explicit

Private WithEvents CmbarsEvent As CommandBars

Private Type POINTAPI
    x As Long
    Y As Long
End Type

#If VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    #If Win64 Then
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal arg1 As LongPtr, ppacc As IAccessible, pvarChild As Variant) As Long
        Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    #Else
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    #End If
#Else
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
#End If
 
Private Const CHILDID_SELF = &H0&
Private Const S_OK As Long = &H0

Private Sub CommandBarButton_Click(ByVal Ctrl As IAccessible)
    MsgBox "You clicked :  '" & Ctrl.accName(CHILDID_SELF) & "'"
End Sub


Private Sub Workbook_Open()
    Set CmbarsEvent = Application.CommandBars
End Sub

Private Sub CmbarsEvent_OnUpdate()
    Const lButtonParentsCount = 10
    Dim lCounter As Long
    Dim oIA As IAccessible
    Dim oIButton As IAccessible
    Dim lResult As Long
    Dim tPt As POINTAPI


    GetCursorPos tPt
    
    #If Win64 Then
        Dim lngPtr As LongPtr
        CopyMemory lngPtr, tPt, LenB(tPt)
        lResult = AccessibleObjectFromPoint(lngPtr, oIA, 0)
    #Else
        lResult = AccessibleObjectFromPoint(tPt.x, tPt.Y, oIA, 0)
    #End If
    
    If lResult = S_OK Then
        On Error Resume Next
        Set oIButton = oIA
        For lCounter = 1 To lButtonParentsCount
            Set oIA = oIA.accParent
        Next
        If TypeName(oIA) = "IAccessible" Then
            If Err.Number = 0 Then
                Call CommandBarButton_Click(oIButton)
            End If
        End If
    End If
End Sub

 

[msdejong79]

Hello again Jaafar and/or snb,

One more functionality I'd like to add: that the macro will also be fired when more cells are selected. As it is now, the macro is executed when the indent icon is clicked, but only if the selection is one cell.

It would be great if the macro would also work when more cells are selected.

(I'm working with the original code provided by Jaafar)

Thanks in advance!

 

[msdejong79]

Please disregard the comment above. It was in fact working, just my poor VBA skills led me to think it wasn't