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!

 

[L. Howard]

Maybe this?

Howard


Using this property to set the indent level to a number less than 0 (zero) or greater than 15 causes an error.

With Range("A10") 
 .IndentLevel = 15 
End With

 

[msdejong79]

Maybe this?

Howard


Using this property to set the indent level to a number less than 0 (zero) or greater than 15 causes an error.

With Range("A10") 
 .IndentLevel = 15 
End With

Hello Howard,

Perhaps I need to clarify: I'd like to have VBA executed when a user uses the commandbar 'Increase Indent' or 'Decrease Indent'.

Thanks in advance.
Maarten

 

[L. Howard]

I'm thinking that is not supported in events.

There are a ton of other events that will play, I could not find Indent in this link to Chip Pearson's info on events.

Events In Excel VBA

Howard

 

[msdejong79]

Indeed, it's not on that page.

The furthest I got was via http://www.mrexcel.com/forum/excel-questions/448695-increase-indent-click-event-using-withevent.html

Anyone want to give it another try?

Thanks in advance!
Maarten

 

[Peter_SSs]

I'm not especially hopeful, but perhaps if you tell us more about what you have on your sheet, where, and what you actually want your macro to do when an indent is changed, maybe somebody might be able to think up a work-around if the direct method you envisage turns out to be unachievable.

 

[msdejong79]

In addition to my previous comment: I am tenacious, because this page from microsoft https://msdn.microsoft.com/en-us/li...e.tools.ribbon.ribbongallery.butt*******.aspx clues me that it is in fact possible: apparently with this piece of coding:

Event[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] Butt******* [/FONT][/COLOR][COLOR=blue][FONT=Consolas]As[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] RibbonControlEventHandler 
[/FONT][/COLOR][COLOR=#0000FF][FONT=Consolas]

Just not sure how to embed it properly in code.

 

[msdejong79]

Hello Peter,

Didn't catch your comment before posting previous message, in which something went wrong too. Sorry about that. Coding should be: Event Butt******* As RibbonControlEventHandler.

What I want is the following: when the user clicks on the indent button, whether it is increase or decrease, it should trigger a calculation immediately. Indent change usually doesn't call for recalculation, because normally it doesn't affect anything, however I use a custom function that I named 'Indent'. So if I could invoke macro on the ribbon indent buttons, if only force calculation, it would be perfect.

I already thought of a workaround: to have automatic recalculation everytime another cell is selected. Seems a bit excessive but perhaps this is the only solution? Also, it poses another challenge, because I'd like to have underlying cells also indented as long as they are 'part' of the above cell. So then it would be vital that the address of previous cell is stored...

 

[snb_]

Why would you want to recalculate a sheet in which nothing has been changed ?

 

[RoryA]

The code you are looking at on MSDN is not VBA, nor are those events available to VBA as far as I know. You would need to modify the CustomUI part of the workbook to override the buttons for increase/decrease indent and then run your calculate code in the callback. You'd also need to replicate the existing functionality in your code.