VBA Enable Ribbon Button on Selection

[decadence]

Hi, Is there a way to enable a ribbon button if a column or row is selected and disable the same button if row or column is not selected.

 

[Jerry Sullivan]

Hi decadence,

Is the button that you want to enable/disable a built-in control or a custom control. If built-in, which button is it? If custom, please describe the control type.

 

[decadence]

Hi Jerry, They are custom controls, I have 4 buttons on a ribbon of which 2 buttons move a Selected row Up or Down and 2 buttons to move a Selected column Left or Right.

What I would like to do is only enable the Left and Right buttons if a column is selected, and only enable the Up and Down buttons if a row is selected. if a column or row is not selected then keep buttons disabled

 

[decadence]

This is the code I'm using currently using

group id="MoveGroups" label="Move"

toggleButton id="MoveCR" size="large" imageMso="DrawingCanvasExpand" getLabel="getToggleLabel" getPressed="getTogglePressed" onAction="onToggleAction" screentip = "This hides or shows Move Buttons" 

group

group id="MoveColumns" tag="GroupMove" getVisible = "getGroupVisible"  label="Column"

button id="MoveColumnLeft" tag="EnableLeft" imageMso="TableColumnsInsertLeft" screentip="Move Column Left" supertip="Move the selected column to the left" label="Left" size="large" onAction="MoveColumnLeft"

button id="MoveColumnRight" tag="EnableRight" imageMso="TableColumnsInsertRight" screentip="Move Column Right" supertip="Move the selected column to the right" label="Right" size="large" onAction="MoveColumnRight"

group

group id="MoveRows" tag="GroupMove" getVisible = "getGroupVisible"  label="Row"

button id="MoveRowUp" tag="EnableUp" imageMso="TableRowsInsertAboveWord" screentip="Move Row Up" supertip="Move the selected row up" label="Up"  size="large" onAction="MoveRowUp"

button id="MoveRowDown" tag="EnableDown" imageMso="TableRowsInsertBelowWord" screentip="Move Row Down" supertip="Move the selected row down" label="Down" size="large" onAction="MoveRowDown"
group

Option Explicit
Public bPressedMoveCR As Boolean, bVisibleMoveCR As Boolean


Sub OnRibb******(ribbon As IRibbonUI)
'
    Set oRibbon = ribbon

    bPressedMoveCR = False
    
End Sub


Sub getToggleLabel(control As IRibbonControl, ByRef returnedVal)
'
    Select Case control.ID

    Case "MoveCR"

        If bVisibleMoveCR Then
            returnedVal = "Hide Move"

        Else

            returnedVal = "Show Move"
        End If

    End Select

End Sub


Sub getTogglePressed(control As IRibbonControl, ByRef returnedVal)
 '
    Select Case control.ID

    Case "MoveCR"
        returnedVal = bPressedMoveCR
        
    End Select

End Sub


Sub onToggleAction(control As IRibbonControl, pressed As Boolean)
'
    Select Case control.ID

        Case "MoveCR"

        	bPressedMoveCR = Not bPressedMoveCR

        	bVisibleMoveCR = Not bVisibleMoveCR

        oRibbon.Invalidate
        
    End Select

End Sub


Sub getGroupVisible(control As IRibbonControl, ByRef returnedVal)
'
    Select Case control.ID

        Case "MoveColumns"
            returnedVal = bVisibleMoveCR
            
         Case "MoveRows"
            returnedVal = bVisibleMoveCR

    End Select

End Sub

 

[Jerry Sullivan]

Thanks, that's helpful.

Are these buttons to be used for only one worksheet or all worksheets in the workbook that has the ribbon code?

 

[decadence]

They are for all worksheets in a workbook

 

[Jerry Sullivan]

Here's some code for you to try.

Modify your RibbonX code to add a getEnabled callback to each of the 4 buttons.
Example for the "MoveColumnRight" button...

      	   <button id="MoveColumnRight" 
			tag="EnableRight" 
			imageMso="TableColumnsInsertRight" 
			screentip="Move Column Right" 
			supertip="Move the selected column to the right" 
			label="Right" 
			getEnabled="GetEnabledMacro"
			size="large" 
			onAction="MoveColumnRight"/>

In the ThisWorkbook Code Module...

Private Sub Workbook_Open()
 Call UpdateMoveRibbonButtons
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 Call UpdateMoveRibbonButtons
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 Call UpdateMoveRibbonButtons
End Sub

Add this to the Code Module that has the other Ribbon procedures. The module-level variable declarations need to be near the top of the module before any Subs or Functions.

Dim oRibbon As IRibbonUI
Dim msDirection As String
  
Sub UpdateMoveRibbonButtons()
 '--if entire row(s) or column(s) are selected, then
 '  enable corresponding ribbon buttons
 
 '--buttons will only be enabled if user selects 1 entire
 '    row or column. Modify these values to allow user to
 '    select more than one row or column
 Const lMAX_COL = 1
 Const lMAX_ROW = 1
 
 With Selection
   Select Case True
      Case TypeName(Selection) <> "Range"
         '--selected object is not range-disable move buttons
         Call RefreshRibbon(sDirection:=vbNullString)
   
      Case .Rows.Count = .Parent.Rows.Count And _
         .Columns.Count <= lMAX_COL
         '--entire column(s) selected
         Call RefreshRibbon(sDirection:="Column")
      
      Case .Columns.Count = .Parent.Columns.Count And _
         .Rows.Count <= lMAX_ROW
         '--entire row(s) selected
         Call RefreshRibbon(sDirection:="Row")
        
      Case Else
         '--disable move buttons
         Call RefreshRibbon(sDirection:=vbNullString)

   End Select
 End With
End Sub

Sub RefreshRibbon(sDirection As String)
' based on https://www.rondebruin.nl/win/s2/win013.htm

 msDirection = sDirection
   
 If oRibbon Is Nothing Then
   MsgBox "Error, Save/Restart your workbook" & vbNewLine & _
   "Visit this page for a solution:" & _
   "https://www.mrexcel.com/forum/excel-questions/518629-how-preserve-regain-id-my-custom-ribbon-ui.html"
 Else
   oRibbon.Invalidate
 End If
 
End Sub

 

[decadence]

Hi Jerry, I have done this but there is a problem with oRbbon.Invalidate, I have followed the link and looked at RoryA and WernerG posts but I'm not sure how I would incorporate this into my code. Can you help with this please?

 Dim oRibbon As IRibbonUI
 Dim msDirection As String
 Public CEnabled As Boolean, REnabled As Boolean, bPressedMoveCR As Boolean, bVisibleMoveCR As Boolean

Sub OnRibb******(ribbon As IRibbonUI)
'
    Set oRibbon = ribbon

    bPressedMoveCR = False
    
    CEnabled = True
    REnabled = True

 End Sub



Sub getToggleLabel(control As IRibbonControl, ByRef returnedVal)
'
    Select Case control.ID
    
    Case "MoveCR"
        If bVisibleMoveCR Then
            returnedVal = "Hide Move"
        Else
            returnedVal = "Show Move"
        End If
    End Select
End Sub


Sub getTogglePressed(control As IRibbonControl, ByRef returnedVal)
 '
    Select Case control.ID
    
    Case "MoveCR"
        returnedVal = bPressedMoveCR
        
     End Select

 End Sub


Sub onToggleAction(control As IRibbonControl, pressed As Boolean)
'
        Case "MoveCR"
    bPressedMoveCR = Not bPressedMoveCR
            bVisibleMoveCR = Not bVisibleMoveCR
        oRibbon.Invalidate
        
    End Select

End Sub

Sub getGroupVisible(control As IRibbonControl, ByRef returnedVal)
'
    Select Case control.ID
    
        Case "MoveColumns"
            returnedVal = bVisibleMoveCR
            
         Case "MoveRows"
            returnedVal = bVisibleMoveCR

    End Select

End Sub

Sub GetEnabledMacro(control As IRibbonControl, ByRef enabled)
'
    Select Case control.ID
    
        Case "MoveColumnLeft"
            enabled = CEnabled

        Case "MoveColumnRight"
            enabled = CEnabled

        Case "MoveRowUp"
            enabled = REnabled

        Case "MoveRowDown"
            enabled = REnabled
            
        Case "MoveColumnLeft"
            enabled = Not (CEnabled)

        Case "MoveColumnRight"
            enabled = Not (CEnabled)

        Case "MoveRowUp"
            enabled = Not (REnabled)

        Case "MoveRowDown"
            enabled = Not (REnabled)
            
    End Select
    
End Sub


Sub UpdateMoveRibbonButtons()
'
    Const lMAX_COL = 1
    Const lMAX_ROW = 1
 
    With Selection

        Select Case True

            Case TypeName(Selection) <> "Range"
                Call RefreshRibbon(sDirection:=vbNullString) '--selected object is not range-disable move buttons

            Case .Rows.Count = .Parent.Rows.Count And .Columns.Count <= lMAX_COL
                Call RefreshRibbon(sDirection:="Column")  '--entire column(s) selected

            Case .Columns.Count = .Parent.Columns.Count And .Rows.Count <= lMAX_ROW
                Call RefreshRibbon(sDirection:="Row") '--entire row(s) selected

            Case Else
                Call RefreshRibbon(sDirection:=vbNullString) '--disable move buttons

        End Select

    End With

End Sub


Sub RefreshRibbon(sDirection As String)
'
    msDirection = sDirection
   
    If oRibbon Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
        oRibbon.Invalidate
    End If

End Sub

<group id="MoveGroups" label="Move">

<toggleButton id="MoveCR" 
		size="large" 
		imageMso="DrawingCanvasExpand" 
		getLabel="getToggleLabel" 
		getPressed="getTogglePressed" 
		onAction="onToggleAction" screentip = "This hides or shows Move Buttons" />

</group>

<group id="MoveColumns" tag="GroupMove" getVisible = "getGroupVisible"  label="Column">

<button id="MoveColumnLeft" 
		tag="EnableLeft" 
		getEnabled="GetEnabledMacro" 
		imageMso="TableColumnsInsertLeft" 
		screentip="Move Column Left" 
		supertip="Move the selected column to the left" 
		label="Left" size="large" 
		onAction="MoveColumnLeft" />

<button id="MoveColumnRight" 
		tag="EnableRight" 
		getEnabled="GetEnabledMacro" 
		imageMso="TableColumnsInsertRight" 
		screentip="Move Column Right" supertip="Move the selected column to the right" 
		label="Right" 
		size="large" 
		onAction="MoveColumnRight" />

</group>

<group id="MoveRows" tag="GroupMove" getVisible = "getGroupVisible"  label="Row">

<button id="MoveRowUp" 
		tag="EnableUp" 
		getEnabled="GetEnabledMacro" 
		imageMso="TableRowsInsertAboveWord" 
		screentip="Move Row Up" supertip="Move the selected row up" label="Up"  
		size="large" 
		onAction="MoveRowUp" />

<button id="MoveRowDown" 
		tag="EnableDown" 
		getEnabled="GetEnabledMacro" 
		imageMso="TableRowsInsertBelowWord" 
		screentip="Move Row Down" supertip="Move the selected row down" 
		label="Down" 
		size="large" 
		onAction="MoveRowDown" />

</group>

 

[Jerry Sullivan]

Rory's code addresses the problem that occurs when the variable oRibbon loses its reference to the ribbon object. This typically occurs when there is an unhandled error. Before Rory developed that code, the only fix was to close and reopen the file so that the Ribbononl_o_a_d procedure could reassign the ribbon to the variable.

I'd suggest first getting your code to work without Rory's code using the longer method of reopening your file if and when the reference to the ribbon is lost. Reopening is inconvenient, but it's simpler than trying to debug two pieces of code at the same time.

I see that you've changed the Sub GetEnabledMacro from the code that I suggested. The modified code doesn't look like it would work (unless you have some additional code that you didn't post).

After the first four cases in the Select Case block, you repeat those cases with another action. There's no scenario in which the one of fifth through eight cases is executed, because if one of those cases is True, one of the first four cases would have been true and execution would have left the Select Case block before reaching the duplicate case.

You are also trying to use CEnabled, REnabled instead of variable msDirection to store the desired state. That's okay, but you haven't changed Sub UpdateMoveRibbonButtons to use those CEnabled, REnabled instead of variable misdirection. I'd suggest you get the code I posted working first and have that serve as a baseline, before trying to modify it.