VBA Excel - customizing ribbonx issue - unhide tab when opening file with filename containing string

jdjehanneman

New Member
Joined
Mar 23, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a problem with my custom ribbon.
I would like Every time a file is opened, whether at Excel startup or via a control in a userform, to check whether the name of the file to be opened contains the string "Manage" and if so, make a custom tag visible.

The check should be done everytime a workbook is opened:
1. When the file was opened from within Excel
2. When a new file is created
2. When opening a file from a custom userform:

VBA Code:
Private Sub bt_OpenManager_Click()
    Dim strTemplateFilePath As String
    Dim wb As Excel.Workbook

    If listbox_Paths_Managers.ListIndex <> -1 Then
        strTemplateFilePath = PATH_CICTOOLS_ADMIN & listbox_Paths_Managers.List(listbox_Paths_Managers.ListIndex)
        FrmTemplateManager.Hide
        Set wkb = Workbooks.Open(strTemplateFilePath)
    End If
End Sub

in the xml code I have put the following code :

VBA Code:
<tab id="MyCustomTab2" label="CIC-Tools Templates" getVisible="GetVisible" tag="cictools_savetemplates">

In the VBA project I have created a module, RibbonModule:

VBA Code:
Dim Rib As IRibbonUI
Dim MyTag As String

' Callback for customUI.onLoad
' make tab cictools_savetemplates visible if opened filename contains "Manage" in the name
Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set Rib = ribbon
    RibPointer = ObjPtr(ribbon)
    If Contains = InStr(ThisWorkbook.Name, "Manage") > 1 Then
        Call RefreshRibbon(Tag:="cictools_savetemplates")
    End If
End Sub

'Called by sheet change event handler (module Sheet1) to make Excel recreate the ribbon
Sub RedoRib()
    If Rib Is Nothing Then
        Set Rib = GetRibbon(RibPointer)
        Rib.Invalidate
        MsgBox "The Ribbon handle was lost, Hopefully this is sorted now by the GetRibbon Function?. You can remove this msgbox, I only use it for testing"
    Else
        Rib.Invalidate
    End If
End Sub

Sub GetVisible(control As IRibbonControl, ByRef visible)
    If control.Tag Like MyTag Then
        visible = True
    Else
        visible = False
    End If
End Sub

Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If Rib Is Nothing Then
        MsgBox "Error, restart your workbook"
    Else
        Rib.Invalidate
    End If
End Sub

When I open the file from the user form the check doesn't work because ThisWorkbook.Name is not the just opened workbook but the .xlam file controling the VBA:
VBA Code:
InStr(ThisWorkbook.Name, "Manage")


The test is never met and my tab is not getting visible.

I hope I am clear enough, it is rather complex to explain.

Thank you in front for your help!

Johan
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Why are you testing Thisworkbook.Name, rather than wkb.Name?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Assuming you want to refer to the active workbook, the syntax would be:

Code:
If InStr(1, ActiveWorkbook.Name, "Manage", vbTextCompare) <> 0 Then
 

jdjehanneman

New Member
Joined
Mar 23, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

After a couple of hours of work I solved some of my issues with the ribbon. By storing the pointer in the registry so I can recreate it anytime. I think Rory most of the code comes from other posts of zou. This solves some other problems I had with the ribbon:

VBA Code:
Option Explicit

Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)

Dim Rib As IRibbonUI
Dim MyTag As String


'Callback for customUI.onLoad
Sub RibbonOnLoad(ribbon As IRibbonUI)
    Dim lRibPointer As LongPtr
    Dim res As Boolean
    Set Rib = ribbon                    'GET RIBBON
    lRibPointer = ObjPtr(ribbon)        'GET RIBBON ID
    res = Functions_SetBFPKey("RibbonPointer", CStr(lRibPointer))   ' Write ribbonpointer to the registry
    MyTag = "cictools"
End Sub

' when Ribbon object is lost -> recreate it using the object pointer stored in the registry
Function getRibbon() As IRibbonUI
    If Rib Is Nothing Then
        Dim ribbonPointer As Long
        ribbonPointer = GetPointer()
        Call CopyMemory(Rib, ribbonPointer, 4)
    End If
    Set getRibbon = Rib
End Function

'make Excel recreate the ribbon from the pointer stored in the registry
Sub RedoRib()
    If Rib Is Nothing Then
        Set Rib = getRibbon()
        Rib.Invalidate
    Else
        Rib.Invalidate
    End If
End Sub

Sub RefreshRibbon(Tag As String, Optional TabID As String)
    MyTag = Tag
    If Rib Is Nothing Then
        Call RedoRib
    Else
        Rib.Invalidate
    End If
End Sub

Sub GetVisible(control As IRibbonControl, ByRef visible)
    If control.Tag Like MyTag Then
        visible = True
    Else
        visible = False
    End If
End Sub

' Get the ribbonpointer from the registry
Function GetPointer()
    Dim tmp As String
    tmp = Functions_GetBFPKey("RibbonPointer")
    GetPointer = CLng(tmp)
End Function

On the other hand I still don't manage to show/hide "MyCustomTab1" and "MyCustomTab2" depending on the name of the file I'm opening. All my code is stored in an .xlam and fires whenever a workbook is opened. I tried to use the Workbook.Open() event:
VBA Code:
Public Sub Workbook_Open()
    If InStr(1, ThisWorkbook.Name, "Manage", vbTextCompare) <> 0 Then
        Call RefreshRibbon(Tag:="cic*")
    End If
End Sub
But nothing happens -> because when opening the workbook with Workbook.Open() ThisWorkbook.Name = the name of the .xlam .
Which object and which event should I use to test if the workbook should have both or just one custom tab activated.

Thx in front
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That Workbook_open is only triggered when your add-in opens. You need to be using application level events to trap any workbook opening. Check (the late, great) Chip Pearson’s website for examples of how to do that.
 

jdjehanneman

New Member
Joined
Mar 23, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Thx Rory for the input. I will check the website of Chip Pearson and look into the Application level events.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top