Solved

Custom Ribbon doesn't refresh

Posted on 2016-09-06
7
110 Views
Last Modified: 2016-09-08
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.
0
Comment
Question by:Leonel Garcia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 15

Expert Comment

by:DrTribos
ID: 41787234
Google for Greg Maxey (MS Word) or Ron de Bruin (MS Excel) both have great examples for custom Ribbons
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 41787385
Under certain circumstances, when your modules run in an unhandled error or recompiles of code files, the values of module level variables are lost.  So you need error handling in all of your modules methods. Even a

On Local Error Resume Next

Open in new window


is sufficient.

Then you should rethink your approach: Use a generic methods and evaluate the control id's. Cause these methods must be otherwise all defined separately using the correct parameter list. So clean-up your code.

E.g.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon> 
		<tabs>		
			<tab id="tabAdministration" label="BPR Moldeo" insertAfterMso="TabView">			
				<group id="grpAcceso" label="Activar sistema" tag="Accede">
					<button id="btnLogIn" label="Log in" size="large" onAction="RibbonOnAction" imageMso="ReviewProtectWorkbook" />
				</group>
				
				<group id="grpGenerarBpr" label="BPRs Moldeo" getVisible="RibbonGetVisible" tag="LGGenerar">
					<button id="btnGenerar" label="Generar" size="large" onAction="RibbonOnAction" imageMso="PivotTableInsert" />
				</group>
				
				<group id="grpNote" label="By I. Q. Leonel Garcia [QA AL]" getVisible="RibbonGetVisible" tag="LGAyuda">
					<button id="btnDudasComentarios" label="Dudas / Comentarios" size="large" onAction="RibbonOnAction" imageMso="FileSendMenu" />
				</group>
				
				<group id="grpExit" label="Desconexión" getVisible="RibbonGetVisible" tag="LGSalir">
					<button id="btnSalir" label="Salir" size="large" onAction="RibbonOnAction" imageMso="WindowClose" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Open in new window


with

Option Explicit

Private m_Ribbon As IRibbonUI
Private m_Tag As String

Public Sub RefreshRibbon(ATag As String)

  On Local Error Resume Next

  MyTag = Tag
  RibbonInvalidate

End Sub

Public Sub RibbonInvalidate()

  On Local Error Resume Next
  
  If m_Ribbon Is Nothing Then
    MsgBox "D'oh!"
  Else
    m_Ribbon.Invalidate
  End If

End Sub

Public Sub RibbonOnLoad(ARibbon As IRibbonUI)

  On Local Error Resume Next
  
  Set m_Ribbon = ARibbon  
     
End Sub

Public Sub RibbonOnAction(AControl As IRibbonControl)
    
  On Local Error Resume Next
	
  Select case AControl.ID
    Case Is = "btnLogIn"
	  LoginMethod
    Case Is = "btnGenerar"
	  GenerarMethod
    Case Is = "btnDudasComentarios"
	  DudasComentariosMethod
    Case Is = "btnSalir"
	  SalirMethod
  End Select 
  
End Sub

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 = True
    Case Is = "grpNote"
	  AVisible = True
    Case Is = "grpExit"
	  AVisible = True
    Case Else
	  AVisible = False
  End Select 
	
End Sub

Open in new window


and

Option Explicit

Public Sub LoginMethod()

  On Local Error Resume Next
    
  ' Your actual code.
	 
End Sub

Public Sub GenerarMethod()

  On Local Error Resume Next
  
  ' Your actual code.
  
End Sub

Public Sub DudasComentariosMethod()

  On Local Error Resume Next
  
  ' Your actual code.
  
End Sub

Public Sub SalirMethod()

  On Local Error Resume Next

  ' Your actual code.  
  
End Sub

Open in new window


The best resource inclusive tools is: WYSIWYG Ribbon Editor from Gunter Avenius.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41787655
@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?
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:Leonel Garcia
ID: 41787667
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
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41787674
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'.
0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 500 total points
ID: 41787684
E.g.

Public Sub RibbonGetVisible(AControl As IRibbonControl, ByRef AVisible)

  On Local Error Resume Next

  If m_Tag = "show" Then
    AVisible = True
  Else
    AVisible = (AControl.Tag Like m_Tag)
  End If
    
End Sub

Open in new window


But instead of using tags, you should consider using the id:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon> 
		<tabs>		
			<tab id="tabAdministration" label="BPR Moldeo" insertAfterMso="TabView">			
				<group id="grpAcceso" label="Activar sistema">
					<button id="btnLogIn" label="Log in" size="large" onAction="RibbonOnAction" imageMso="ReviewProtectWorkbook" />
				</group>
				
				<group id="grpGenerarBpr" label="BPRs Moldeo" getVisible="RibbonGetVisible">
					<button id="btnGenerar" label="Generar" size="large" onAction="RibbonOnAction" imageMso="PivotTableInsert" />			
					<separator id="grpGenerarBpr_Separator_1" />
					<button id="btnDudasComentarios" label="Dudas / Comentarios" size="large" onAction="RibbonOnAction" imageMso="FileSendMenu" />
					<separator id="grpGenerarBpr_Separator_2" />
					<button id="btnSalir" label="Salir" size="large" onAction="RibbonOnAction" imageMso="WindowClose" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Open in new window


with

Public Sub RibbonGetVisible(AControl As IRibbonControl, ByRef AVisible)

  On Local Error Resume Next

  If m_Tag = "show" Then
    AVisible = True
  Else
    AVisible = (AControl.ID = m_Tag)
  End If
    
End Sub

Open in new window


and use the group id, instead of the tag value.
0
 

Author Closing Comment

by:Leonel Garcia
ID: 41789992
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
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
EXCEL Name Matching 13 45
Outlook 2013 Contacts - comma-separated-field 20 35
Tricky Shapes formula 3 18
Calculate Weeks in A "Fiscal Month" 8 26
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question