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="" onLoad="ribbonOnLoad">
      <ribbon startFromScratch="false">
    	  <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"/>

    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
            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
    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(
        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(
            Case "eb1"
                 gStr = s
            End Select
        End If
    End Sub
    Sub bCallback(control As IRibbonControl)
        Select Case LCase(
        Case "b1"   'button click
        End Select
    End Sub

    Sheet1 VBA code

    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