VBA Enable Ribbon button by Selection

Question

I am trying to figure out how I can enable a ribbon button based on Selection, I know I need to use Worksheet_SelectionChange Event however I not sure how to proceed. I have exhausted all options looking on how to do it, can someone help with this please, I have asked on Mr Excel but no answers to what I need. Example of what I am looking for is:

If a column is selected then enable ribbon button a or If a Row is selected then Enable ribbon button b

do you know how to enable the ribbon button at all? Please share the code you have so far. There are two steps here: make some happen when selection changes, and enable ribbon button. Each of those steps have many existing examples, please make an effort. — ashleedawg, Apr 26 '18 at 12:10

score 0 · Answer 1 · answered Apr 26 '18 at 13:05

Use ribbon callbacks and call IRibbonUI.Invalidate or IRibbonUI.InvalidateControl where appropriate. See How to get the reference to the IRibbonUI in VBA? for more information.

You can customize the Ribbon UI by using callback procedures in VBA macros or COM add-ins. For each of the callbacks the add-in implements, the responses are cached. For example, if an add-in writer implements the getImage callback procedure for a button, the function is called once, the image loads, and then if the image needs to be updated, the cached image is used instead of recalling the procedure. This process remains in-place until the code signals that the cached values are invalid by using the Invalidate method, at which time, the callback procedure is again called and the return response is cached. The add-in or VBA macros can then force an immediate update of the UI by calling the Refresh method.

In your custom UI XML file you need to declare the onLoad callback:

<customUI … onLoad=”MyAddInInitialize” …>

And then in VBA you could use:

Dim MyRibbon As IRibbonUI
Sub MyAddInInitialize(Ribbon As IRibbonUI)
   Set MyRibbon = Ribbon
End Sub

Sub myFunction()
   ‘ Invalidates the caches of all of this add-in’s controls 
   MyRibbon.Invalidate()            
End Sub

Read more about the Fluent UI (aka Ribbon UI) in the following articles:

score 0 · Answer 2 · edited Mar 30 '23 at 11:47

This is what I have so far but I'm still a novice.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad">
    <ribbon>
        <tabs>      
            <tab id="MyTools" label="Tools">
                <group id="MoveGroup" label="Move" tag="GroupMove" >
                    <button id="MoveColumnLeft" tag="EnableLeft" imageMso="GoRtl" screentip="Move Column Left" supertip="Move the selected column to the left" label="Left" size="large" onAction="OnActionButton" getEnabled="GetEnabled" />
                    <button id="MoveColumnRight" tag="EnableRight" imageMso="GoLeftToRight" screentip="Move Column Right" supertip="Move the selected column to the right" label="Right" size="large" onAction="OnActionButton" getEnabled="GetEnabled" />
                    <separator id="MoveSep" />
                    <button id="MoveRowUp" tag="EnableUp" imageMso="MessagePrevious" screentip="Move Row Up" supertip="Move the selected row up" label="Up"  size="large" onAction="OnActionButton" getEnabled="GetEnabled" />
                    <button id="MoveRowDown" tag="EnableDown" imageMso="MessageNext" screentip="Move Row Down" supertip="Move the selected row down" label="Down" size="large" onAction="OnActionButton" getEnabled="GetEnabled" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Option Explicit

 Public oRibbon As IRibbonUI, bEnabled As Boolean

Sub OnRibbonLoad(ribbon As IRibbonUI)
    Set oRibbon = ribbon
    bEnabled = True
End Sub

Sub OnActionButton(control As IRibbonControl)
    Select Case control.ID
        Case "MoveColumnLeft"
            bEnabled = enabled
            oRibbon.Invalidate
        Case "MoveColumnRight"
            bEnabled = enabled
            oRibbon.Invalidate
    End Select
End Sub


Sub GetEnabled(control As IRibbonControl, ByRef enabled)
    Select Case control.ID
        Case "MoveColumnLeft"
            enabled = enabled
        Case "MoveColumnRight"
            enabled = enabled
    End Select
End Sub

VBA Enable Ribbon button by Selection

2 Answers2