none
dynamic change of ribbon edit box does not work until VBA macro completes RRS feed

  • Question

  • I have a VBA macro that loops while a user enters text using inputbox. This text is then entered into a cell. the Worksheet_Change sub is then used to set a global string variable and then invalidate the ribbooncontrol. Nothing happens until the VBA macro is terminated.

    Does anyone know of a way to force the ribbon text to update in real-time?

    Ribbon HTML

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="ribbonOnLoad">
      <ribbon startFromScratch="false">
        <tabs>
    	  <tab id="MyTab" label="MyTab" insertBeforeMso="TabHome">
                 <group id="g1" label="MyGroup">
                    <button id="b1" label="MyButton" imageMso="MoviePlay" size="normal" onAction="bCallback" />				
                   <editBox id="eb1" getText="ebGetText" label="MyString" onChange="ebOnChange" sizeString="WWWW"/>
    	     </group>
            </tab>
        </tabs>
      </ribbon>
    </customUI>

    VBA code module

    ' module = ribbonStuff
    Option Explicit
    'object to contain the LMC ribbon
    Public gRibbon As IRibbonUI
    Public gStr As String
    
    Sub peterLoop()
        Dim i As Integer
        i = 0
        Dim x As String
        Do
            i = i + 1
            x = InputBox(i & ". Supply a string", "String required")
            If x = "" Then Exit Do
            gStr = x
            ThisWorkbook.Worksheets(1).Cells(1, 1).Value = gStr
           'gRibbon.InvalidateControl "eb1" 'this doesn't work either
        Loop
    End Sub
    
    Sub activateMyTab()
        gRibbon.ActivateTab "MyTab"
    End Sub
    
    Sub ribbonOnLoad(ByVal ribbon As office.IRibbonUI)
        'when workbook opens it sets the ribbon
        Set gRibbon = ribbon
    End Sub
    
    Sub ebGetText(control As IRibbonControl, ByRef retVal)
        Select Case LCase(control.id)
        Case "eb1"
           retVal = gStr
        End Select
    End Sub
    
    Sub ebOnChange(control As IRibbonControl, text As String)
        Dim s As String
        s = Trim(text)
        If s <> "" Then
            Select Case LCase(control.id)
            Case "eb1"
                 gStr = s
            End Select
        End If
    End Sub
    
    
    Sub bCallback(control As IRibbonControl)
        Select Case LCase(control.id)
        Case "b1"   'button click
             peterLoop
        End Select
    End Sub
    

    Sheet1 VBA code

    'sheet1
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error Resume Next
        If Target.Cells.Count > 1 Then Exit Sub
        gStr = CStr(Target.Value)
        gRibbon.InvalidateControl "eb1"
    End Sub
    

    Thursday, December 12, 2019 3:49 PM