Changing the properties of elements in Office Ribbon using VBA

Question

I am a beginner in VBA and Office Ribbon UI. I am using Office 2007 and used Custom UI to develop a Ribbon in PPTM. I have added a set of XML like the one below:

<group id="myGroup" label="Hello World" visible="false">
    <labelControl id="lblUsername" label="Your Username: " />
    <labelControl id="lblFullname" label="" />
</group>

So, in this Hello World tab, I would like to change its visibility to true and change the the values of lblUsername and lblFullname. Currently this has to be done after the previous call by this button:

<button id="signin" label="Sign In" image="signin" size="large"
    supertip="Click this button to sign in."
    onAction="ribbon_SignIn" tag="SignIn" />

Right now the code in the ribbon_SignIn is as follows:

Sub ribbon_SignIn()
    SignIn.Show
End Sub

This opens the SignIn form and gets the Username and Password from the user. After the username and password are validated, everything goes fine, but I am not sure what is the procedure to get the properties of controls lblUsername and lblFullname to change their values with the signed in user's details.

---

Clarification

In the SignIn form I have the below code for the Sign In button.

Private Sub btnSignIn_Click()
    ' Authentication Mechanism
    MsgBox "You have successfully signed in!"

    ' Show the Ribbon group.
    ' What am I supposed to do here to make the group visible?
    ' Also how do I change the text of the label?
End Sub

So, here what should I put to make the group visible? Also how do I change the text of the label?

---

Update #1

When I use the two attributes getVisible and getLabel in the Custom UI, the add-in itself is not getting displayed. :( The code that I used was:

<group id="myGroup" label="Hello World" getVisible="VisibleGroup">
    <labelControl id="lblUsername" label="Your Username: " getLabel="lblUsername" />
    <labelControl id="lblFullname" label="" getLabel="lblFullname" />
</group>

If I remove those two attributes, Weird. BTW, I am using Office 2007.

Answer 1

You can use something called the getLabel property in your xml.

e.g.

getLabel = "GetLabelMacro"

Now in your GetLabelMacro you can put in code as

Sub GetLabelMacro(control As IRibbonControl, ByRef label)
    if control.id = "MyLabel" then
        label = "New Label"
    end if
End Sub

You can modify this sample code for your purposes. More info on the syntax of getLabel can be found by googling about the getLabel callback.

Answer 2

Here is a simple, functional example. I substitute simple InputBox prompts instead of trying to recreate your UserForm code, but the general principal should still apply.

I create public variables for username and fullname and bAuthenticated (have your form assign to these variables, or reference the form directly). Set bAuthenticated = True once you have authenticated the user.

Use the getLabel callback and refresh the ribbon. I create two separate callbacks, one for username, one for fullname, these are getUserName and getFullName. I also add the vba for VisibleGroup which is called from the getVisible XML property.

Note when making changes to your VBA you will likely have to save, close & re-open the file, because making these changes may clear all public variables, including the variable which represents the ribbon itself. This is why you might be getting errors from the RefreshRibbon procedure.

Your xml might look like this.

Updated to include the getVisible callback

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonOnLoad">
    <ribbon startFromScratch="false" >
        <tabs>
            <tab id="customTab" label="Custom Tab">
                      <group id="myGroup" label="Hello World" getVisible="VisibleGroup" >
                          <labelControl id="lblUsername" getLabel="getUserName" />
                          <labelControl id="lblFullname" getLabel="getFullName" />
                      </group>
                      <group id="mySignin" label="SignIn" visible="true" >
                          <button id="signin" label="Sign In" size="large"
                              supertip="Click this button to sign in."
                              onAction="ribbon_SignIn" tag="SignIn" />
                      </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Note: I have set the visible property of myGroup to True so that I could more easily test this. You can use the getVisible callback similarly, if you need to change this property at run-time.

And then the callbacks in VBA module will look more or less like:

Option Explicit
Public Rib As IRibbonUI
Public xmlID As String
Public username As String
Public fullName As String
Public bAuthenticated As Boolean

'Callback for customUI.onLoad
Sub RibbonOnLoad(ribbon As IRibbonUI)
    'MsgBox "onLoad"
    Set Rib = ribbon
End Sub

Sub ribbon_SignIn(control As IRibbonControl)

    username = InputBox("enter your username", "Your username?")
    fullName = InputBox("enter your full name", "Your full name?")

    'Authenticate
    bAuthenticated = True

    VisibleGroup control, bAuthenticated
End Sub
Sub VisibleGroup(control As IRibbonControl, ByRef returnedVal)
    returnedVal = bAuthenticated
    Call RefreshRibbon("myGroup")
End Sub
Sub getUserName(control As IRibbonControl, ByRef returnedVal)
    returnedVal = username
    Call RefreshRibbon(control.id)
End Sub
Sub getFullName(control As IRibbonControl, ByRef returnedVal)
    returnedVal = fullName
    Call RefreshRibbon(control.id)
End Sub
Sub RefreshRibbon(id As String)
    xmlID = id
    'MsgBox "Refreshing ribbon for " & Id, vbInformation
    If Rib Is Nothing Then
        MsgBox "Error, Save/Restart your Presentation"
    Else
        Rib.Invalidate
    End If
End Sub