Modifying the custom ribbon with VBA

Damian86

New Member
Joined
Jun 30, 2014
Messages
47
Hi,

I have a custom ribbon with controls that I have tags associated to. I can change visibility of groups and controls by changing the tag property:
Code:
Call RefreshRibbon(Tag:="MyGroup1")
I was wondering if there is a possibility of making the same by not changing the tag property, but directly addressing a control and modifying its properties.

Can you help me?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Afraid not, you have to use callbacks to set the visibility of controls on the Ribbon.
 
Last edited:

Damian86

New Member
Joined
Jun 30, 2014
Messages
47
Thanks RoryA.

But is there a way to use a callback without referring to the "tag" property? I would like to refer to a label, for example. Is there such possibility?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You can invalidate a control specifically by its id using the Ribbon object's InvalidateControl method.
 

Damian86

New Member
Joined
Jun 30, 2014
Messages
47
Could you please provide me with the code?

Now I use:
Code:
Option Explicit
Dim Rib As IRibbonUI
Public MyTag As String

Sub Ribbon******(ribbon As IRibbonUI)
    Set Rib = ribbon
End Sub

Sub GetVisible(Control As IRibbonControl, ByRef visible)
    
    If MyTag = "MyTag" Then
    visible = True
    Else
    If Control.Tag Like MyTag Then
    visible = True
    Else
    visible = 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"
    Else
        Rib.Invalidate
    End If
End Sub
How can I relate to its ID?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You'd pass the ID as a string instead of the tag. How would you determine whether to show or hide the control?
 

Damian86

New Member
Joined
Jun 30, 2014
Messages
47
Thanks RoryA. Maybe I wasn't clear enough from the beginning.

When I use...
Code:
Call RefreshRibbon(Tag:="MyGroup1*")
...for example I get all tags like MyGroup1, MyGroup15, MyGroup125 etc. visible or hidden. What I would like to do is to address a specific control, let's say by referring to it's tag. When I use...
Code:
Call RefreshRibbon(Tag:="MyGroup125")
... I get the MyGroup125 visible, but all other groups are hidden. I would like to manipulate a single control by referring to a tag or id. Tags, the way I use them, work globally, so when I update the ribbon, it shows/hides controls referring to a tag I set up.
I would like to do something like:
Code:
If Control.Tag Like "Group125" Then
visible = false
End If
... so that only one control tagged as "Group125" would be modified.

I hope I was clear enough :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If I understand you correctly, you could do something like this:

Code:
Sub RefreshRibbon(Tag As String, Optional ID as string = vbNullString)
    MyTag = Tag
    If Rib Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
       if ID = vbNullstring then
        Rib.Invalidate
       else
        Rib.InvalidateControl ID
       end if
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,090,030
Messages
5,411,957
Members
403,407
Latest member
craigey1

This Week's Hot Topics

Top