Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and exploring the core foundations for app libraries.
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:
And the following code in my VBA project (not developed for me, as I mentioned I'm new on this)
Module Ribbon
Module Buttons
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.
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>
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
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
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 15
Google for Greg Maxey (MS Word) or Ron de Bruin (MS Excel) both have great examples for custom Ribbons
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
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.
with
and
The best resource inclusive tools is: WYSIWYG Ribbon Editor from Gunter Avenius.
On Local Error Resume Next
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>
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
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
The best resource inclusive tools is: WYSIWYG Ribbon Editor from Gunter Avenius.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
@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.
with
Sorry not understanding, perhaps there's a typo?
Also your comments are confusing (at least they are to me)....
Clean up your code, e.g.
<Ribbon XML>
with
<2 VBA Functions>
Sorry not understanding, perhaps there's a typo?
Hi ste5an many thanks for your answer, I've reviewed it and definitely is a better approach, I've set this part as follows:
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
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
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
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'.
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'.
E.g.
But instead of using tags, you should consider using the id:
with
and use the group id, instead of the tag value.
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
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>
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
and use the group id, instead of the tag value.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Adva… 188 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Basic 239 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Inter… 218 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2010 Adva… 183 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2010 Basic 264 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2010 Inter… 277 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2013 Part … 176 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2013 Part … 146 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2016 Part … 212 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.