lundi 23 octobre 2023

VBA Custom Ribbon - Checkbox Error when enabling editbox

I've put in a custom checkbox and a custom editbox in my ribbonUI where the checkbox enables the editbox when pressed. However, when I press the checkbox is returns with the error 'type mismatch' and the checkbox does not remain clicked. What is also weird is that the editbox does enable even though the error fires.

My XML for the custom ribbon UI is as per below -

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
 
   <ribbon> 
     <tabs> 
 <group id="Process">
            <checkBox id="ProcessVariable"
                      label = "Send Email?"
                      onAction = "ExportRun.CheckBox_OnAction"
                      getPressed="ExportRun.CheckBox_OnAction"/>
                      
            <editBox id="ConsignmentNumber"
                     label="Consignment Number"
                     tag="textbox1"
                     getEnabled ="GetEnabledMacro"
                     onChange="EditBox_OnChange"/>
         
         </group>
       </tab> 
     </tabs> 
   </ribbon> 
 </customUI> 

My VBA code is as follows -

Dim Rib As IRibbonUI
Public MyTag As String

Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set Rib = ribbon
End Sub

Sub GetEnabledMacro(control As IRibbonControl, ByRef Enabled)
    If MyTag = "Enable" Then
        Enabled = True
    Else
        If control.Tag Like MyTag Then
            Enabled = True
        Else
            Enabled = False
        End If
    End If
End Sub

Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If Rib Is Nothing Then
        MsgBox "Error, Save/Restart your workbook" & vbNewLine

    Else
    
        Rib.Invalidate
        
    End If
End Sub

Public Sub Checkbox_OnAction(ByVal control As IRibbonControl, pressed As Boolean)

If pressed Then

        Call EnableTextBoxControl

    Else

        Call DisableTextBoxControl

End If



End Sub

Sub EnableTextBoxControl()

Call RefreshRibbon(Tag:="textbox1")

End Sub

Sub DisableTextBoxControl()

Call RefreshRibbon(Tag:="")

End Sub

How do I avoid this error?




Aucun commentaire:

Enregistrer un commentaire