asked on

Custom Ribbon doesn't refresh

First of all many thanks for take some time to read my question.

I'm new with XML-VBA (actually is my 1st incursion on this)

I've create the following custom ribbon for an Excell addin:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
 <ribbon> 
<tabs>

<!-- Creamos tab para el administrador con sus botones-->

<tab id="customTab" label="BPR Moldeo" insertAfterMso="TabView">

<!-- Creamos grupo de botones 2 "Acceso"-->
				
				<group id="customGroup2" label="Activar sistema" tag="Accede">
					<button id="customButton12" label="Log in" size="large" onAction="sesion" imageMso="ReviewProtectWorkbook" />
					

</group>


<!--Group 0 "Generar BPR"-->
				
				<group id="customGroup0" label="BPRs Moldeo" getVisible="GetVisible" tag="LGGenerar">
					<button id="customButton0" label="Generar" size="large" onAction="Inicio" imageMso="PivotTableInsert" />
					

</group>



<!-- Group Note-->

				<group id="customGroup1" label="By I. Q. Leonel Garcia [QA AL]" getVisible="GetVisible" tag="LGAyuda">
					<button id="customButton11" label="Dudas / Comentarios" size="large" onAction="Envia_Mail" imageMso="FileSendMenu" />
				
				</group>

<!-- Group Exit-->

				<group id="customGroup3" label="Desconexión" getVisible="GetVisible" tag="LGSalir">
					<button id="customButton13" label="Salir" size="large" onAction="Oculta" imageMso="WindowClose" />
				
				</group>

			</tab>
		</tabs>
	</ribbon>
</customUI>

Open in new window

And the following code in my VBA project (not developed for me, as I mentioned I'm new on this)

Module Ribbon

Option Explicit

Public Rib As IRibbonUI
Public MyTag As String

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

Sub GetVisible(control As IRibbonControl, ByRef visible)
    If MyTag = "show" 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, reinicia Excel"
    'Else
        Rib.Invalidate
    'End If
End Sub

Open in new window

Module Buttons

Sub ShowAllButtons()
'Show All
    Call RefreshRibbon(Tag:="*")
End Sub

Sub HideButtons()
'Hide all with a Tag that start with "LG"
    Call RefreshRibbon(Tag:="Login")
End Sub

Sub Oculta(control As IRibbonControl)
Call HideButtons
End Sub

Open in new window

All works fine when I start Excel, the addin is loaded, the custom "test Tab" is showed, once the login is completed three additional buttons are showed, my problem is that after I press button "Generar" or "Dudas/comentarios" the macro assigned to these run OK and when I press the "Salir" button to hide the buttons but I received the notification that something went wrong (Runtime error 91: Object variable or with block variable not set), I've reviewed and looking for information but I have not find nothing related.

Many thanks in advance for your help.

DrTribos

Google for Greg Maxey (MS Word) or Ron de Bruin (MS Excel) both have great examples for custom Ribbons

ASKER CERTIFIED SOLUTION

ste5an

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

DrTribos

@ste5an are you actually advocating On Error Resume Next?

Also your comments are confusing (at least they are to me)....

Clean up your code, e.g.

<Ribbon XML>

Open in new window

with

<2 VBA Functions>

Open in new window

Sorry not understanding, perhaps there's a typo?

Leonel Garcia

ASKER

Hi ste5an many thanks for your answer, I've reviewed it and definitely is a better approach, I've set this part as follows:

Public Sub RibbonGetVisible(AControl As IRibbonControl, ByRef AVisible)

  On Local Error Resume Next

  ' Here you can check preconditions (m_Tag) for each button.
  Select Case AControl.ID
    Case Is = "grpGenerarBpr"
      AVisible = False
    Case Is = "grpNote"
      AVisible = False
    Case Is = "grpExit"
      AVisible = False
    Case Else
      AVisible = False
  End Select
    
End Sub

Open in new window

In this way when file opens I only can see login group as I need, my doubt is how can I proceed in order to show the three groups (grpGenerarBpr, grpNote and grpExit) once the macro assigned to button btnLogIn finishes?.

Many thanks in advance for your help.

Best regards

DrTribos

Hi ste5an

In response to your recently deleted post, I probably misinterpreted - looked like you were suggesting that the Ribbon XML could be replaced with VBA....

Clean up this <XML> with <VBA>; i.e. I thought replace this <XML> with <VBA>. Which, obviously is not what you meant. Guessing the 'with' should have been an 'and'.

SOLUTION

ste5an

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Leonel Garcia

ASKER

Many thanks, the direction and clarity of the answer help me to achieve the objective, all works great an now I've learned many new things, many thanks again