?
Solved

How to refresh changes to the ribbon?

Posted on 2011-03-01
17
Medium Priority
?
4,477 Views
Last Modified: 2013-11-28
Per the following link, I'm trying to refresh changes I made to the Access 2007 ribbon  in the below sub.  But everything I try even after putting in the reference per blog doesn't work.  I'm also unable to see the images or download the sample database.  Thus the question.

http://blogs.office.com/b/microsoft-access/archive/2006/07/13/customizing-the-new-access-ui.aspx

The following attempt balks at line:  gobjRibbon.Invalidate with run-time error 91 object variable or with block variable not set.  

I do not want this merely to just work but rather have syntax of the code to do the late-binding where I would not need to use a reference to the Microsoft Object library as he is saying per blog.
Public Sub Set_Procure_Ribbon(bValue As Boolean)
On Error GoTo Err_This

  Dim sSQL As String
  Dim rs As DAO.Recordset
  Dim sSQLMemo As String
  Dim sReplaceFrom As String
  Dim sReplaceTO As String
  Dim sBackEnd As String
  Dim oAccess As Object
  Dim iArea As Integer
  Dim ribbon As IRibbonUI
  Dim gobjRibbon As IRibbonUI
  
  iArea = 1
  sBackEnd = Replace(Network_Path(CurrentDb.Name) & Right(CurrentDb.Name, Len(CurrentDb.Name) - InStrRev(CurrentDb.Name, "\")), ".accdb", "_be.accdb")
  iArea = 2
  sSQL = "SELECT * FROM [USysRibbons]"
  iArea = 3
  Set rs = CurrentDb.OpenRecordset(sSQL)
  'SET memofield = memofield & Chr(13) & Chr(10) & 'new text'
  iArea = 14
  If Not rs.EOF Then
    iArea = 15
    sSQLMemo = rs.Fields("RibbonXML")
  End If
  rs.Close
  iArea = 16
  Set rs = Nothing
  iArea = 17
  If Not sSQLMemo = "" Then
    iArea = 18
    sReplaceFrom = "label=" & Chr(34) & "EC Procurement" & Chr(34) & " visible=" & Chr(34) & "false"
    iArea = 19
    sReplaceTO = "label=" & Chr(34) & "EC Procurement" & Chr(34) & " visible=" & Chr(34) & "true"
    iArea = 20
    If bValue = True Then
      iArea = 21
      sSQLMemo = Replace(sSQLMemo, sReplaceFrom, sReplaceTO)
    Else
      iArea = 22
      sSQLMemo = Replace(sSQLMemo, sReplaceTO, sReplaceFrom)
    End If
    iArea = 23
    sSQL = "UPDATE [USysRibbons] SET [RibbonXML] = '" & sSQLMemo & "'"
    iArea = 24
    CurrentDb.Execute sSQL
    iArea = 25
    DoEvents
    iArea = 26
    If FileExists(sBackEnd) = True Then
      iArea = 27
      Set_Databases_Off (True)
      iArea = 28
      Set oAccess = CreateObject("Access.Application")
      iArea = 100
      oAccess.OpenCurrentDatabase (Network_Path(CurrentDb.Name) & Right(CurrentDb.Name, Len(CurrentDb.Name) - InStrRev(CurrentDb.Name, "\"))), True
      iArea = 110
      oAccess.Visible = False
      iArea = 120
      oAccess.DoCmd.DeleteObject acTable, "USysRibbons"
      iArea = 130
      oAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", "Q:\EC\0MSAccess\EC_Templates_Reqs.accdb", acTable, "USysRibbons", "USysRibbons"
      iArea = 140
      oAccess.CloseCurrentDatabase
      iArea = 150
      Set oAccess = Nothing
      iArea = 160
      Set_Databases_Off (False)
    End If
    
  End If
  
  
  Set gobjRibbon = ribbon

  gobjRibbon.Invalidate

  Set gobjRibbon = Nothing
  
Exit_This:
  Exit Sub
  
Err_This:
  Set_Databases_Off (False)
  
  If iArea = 100 Then
    On Error Resume Next
    oAccess.CloseCurrentDatabase
    Set oAccess = Nothing
  End If
  Call Error_Action(Err, Err.description, "modRibbon @ Set_Procure_Ribbon @ iArea: " & iArea, Erl())
  Err = 0
  Resume Exit_This
  
End Sub

Open in new window

0
Comment
Question by:stephenlecomptejr
[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
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 6

Accepted Solution

by:
Gugro earned 668 total points
ID: 35008068
When creating the Ribbon XML description you should include the onLoad property:
    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad" loadImage="LoadImages">

and in your VBA code there should be a callback procedure:

    Public gobjRibbon As IRibbonUI

    Sub OnRibbonLoad(ribbon As IRibbonUI)
         ' Callbackname in XML File "onLoad"
        Set gobjRibbon = ribbon
    End Sub

Now, after loading the Ribbon you will have a handle to this ribbon in your global variable gobjRibbon.
After modifying the ribbon you may refresh the changes with:
   
    gobjRibbon.Invalidate
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35008361
Won't running the compact Repair utility refresh the Ribbon?

I mean if you are making Ribbon changes on the fly, you are obviously only going to need to do this manually every once in a while...
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35008793
Gugro, thank you for posting the Ribbon XML description-
I could not see that in the blog.

I tried pasting that in the top of my Ribbon XML description - and I got the following error shown in image1.  The second image shows what I have in my USysRibbon.

Option Compare Database
Option Explicit

Public gobjRibbon As IRibbonUI

Public Sub OnRibbonLoad(ribbon As IRibbonUI)
     ' Callbackname in XML File "onLoad"
    Set gobjRibbon = ribbon
End Sub

Public Sub Set_Procure_Ribbon(bValue As Boolean)
On Error GoTo Err_This

  Dim sSQL As String
  Dim rs As DAO.Recordset
  Dim sSQLMemo As String
  Dim sReplaceFrom As String
  Dim sReplaceTO As String
  Dim sBackEnd As String
  Dim oAccess As Object
  Dim iArea As Integer
  
  iArea = 1
  sBackEnd = Replace(Network_Path(CurrentDb.Name) & Right(CurrentDb.Name, Len(CurrentDb.Name) - InStrRev(CurrentDb.Name, "\")), ".accdb", "_be.accdb")
  iArea = 2
  sSQL = "SELECT * FROM [USysRibbons]"
  iArea = 3
  Set rs = CurrentDb.OpenRecordset(sSQL)
  'SET memofield = memofield & Chr(13) & Chr(10) & 'new text'
  iArea = 14
  If Not rs.EOF Then
    iArea = 15
    sSQLMemo = rs.Fields("RibbonXML")
  End If
  iArea = 16
  Set rs = Nothing
  iArea = 17
  If Not sSQLMemo = "" Then
    iArea = 18
    sReplaceFrom = "label=" & Chr(34) & "EC Procurement" & Chr(34) & " visible=" & Chr(34) & "false"
    iArea = 19
    sReplaceTO = "label=" & Chr(34) & "EC Procurement" & Chr(34) & " visible=" & Chr(34) & "true"
    iArea = 20
    If bValue = True Then
      iArea = 21
      sSQLMemo = Replace(sSQLMemo, sReplaceFrom, sReplaceTO)
    Else
      iArea = 22
      sSQLMemo = Replace(sSQLMemo, sReplaceTO, sReplaceFrom)
    End If
    iArea = 23
    sSQL = "UPDATE [USysRibbons] SET [RibbonXML] = '" & sSQLMemo & "'"
    iArea = 24
    CurrentDb.Execute sSQL
    iArea = 25
    DoEvents
    iArea = 26

  gobjRibbon.Invalidate

  
Exit_This:
  Exit Sub
  
Err_This:
  Set_Databases_Off (False)
  
  If iArea = 100 Then
    On Error Resume Next
    oAccess.CloseCurrentDatabase
    Set oAccess = Nothing
  End If
  Call Error_Action(Err, Err.Description, "modRibbon @ Set_Procure_Ribbon @ iArea: " & iArea, Erl())
  Err = 0
  Resume Exit_This
  
End Sub

Open in new window

xml-error.png
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35008926
OK I got the XML code fixed without giving off an error.
This is what I got.  I was accidently erasing the second part of
 <ribbon startFromScratch="false">

without deleting the </ribbon>
Instead of removing the <ribbon> part at all I left it in there and put the correct -
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad" loadImage="LoadImages">
as stated per Gugro:

So I set the reference to Microsoft Object 12.0 LIbrary and everything about the VBA being in a separate module is shown above.  And I no longer get an error - on the line item gobjRibbon.Invalidate it goes through (using debugging and stepping through) runs just fine.

The problem is that it just doesn't refresh the ribbon at the top at all.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35009121
I've got this article as well:
http://itexcellence.spaces.live.com/blog/cns!50810C099C90C99A!182.entry

But I've been unable to find an error that would render the global property useless.
And of course at least I would get the error when gobjRibbon.Invalidate is ran but I get no error - it goes through just fine,
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35009685
Did you try running the Compact/Repair utility?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35014195
Yes.

But the whole point in doing this with code is so they don't have to click on multiple options.  Even compact and repair is not a single - click option - you have to click on the BOB button - Select Manage - then slide over to Compact and Repair.

Why click in one area to adjust the ribbon and then have go to another place to merely compact and repair?

Here's a guy thats figured how to refresh the ribbon:
http://www.accessribbon.de/en/index.php?Downloads#15
I'm trying to take a part the sample to see how its done... but I can't get to the tables.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35014286
<but I can't get to the tables.> which table? systems table?

office button> access options >current database >Navigations options > show system objects
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35014346
Here's a sample I'm trying to make work.

In it I go and open up the table USysRibbons and manually type and find the term:

"EC Procurement" visible="true"

and change it to


"EC Procurement" visible="false"

Then I move to another field and close out of USysRibbons - then double-click and run the frmRefreshRibbon which upon opening is supposed to run the  gobjRibbon.Invalidate to refresh the ribbon but yet that doesn't happen.  Only when I close and re-open the database then I get the EC Procurementn tab to disappear.

Why would this sample not work even though I have everything just right?
RibbonDatabase.accdb
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35014351
Hey caps, was that for another question and this by accident?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35014359
sorry, wrong thread....
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35014422
<But the whole point in doing this with code is so they don't have to click on multiple options.  Even compact and repair is not a single - click option - you have to click on the BOB button - Select Manage - then slide over to Compact and Repair.>
"so they don't have to click on multiple options"
Are you saying that you are allowing the users to modify the ribbon on the fly?
This is potentially more dangerous than allowing users full access to the DB...

Up to you really...

Even so, why are they changing the ribbon so often that doing a compact /repair is a burden?

Remember, you asked:
<How to refresh changes to the ribbon>
You made not mention of not having "to click on multiple options"
You just asked: <How to refresh changes to the ribbon>

JeffCoachman

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35016248
boag2000,

Thank you for your replies thus far.
I'm clarifying the question by providing a sample.  Were you able to take a look at it and make it work?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1332 total points
ID: 35018067
Nope

Again, I use C&R...
That works as you originally specified.

<<But the whole point in doing this with code is so they don't have to click on multiple options.  Even compact and repair is not a single - click option - you have to click on the BOB button - Select Manage - then slide over to Compact and Repair.>
Remember you can use the Hotkey:
Alt+FMC

FWIW, This is the reason I put all my functionality on forms...
Then I can easily control what options display and when.
This way I avoid all of these "Ribbon/XML, issues"...

Don't get me wrong, I like to embrace new technology as well,

But the time it would take me to create all of the functionality you created with a Ribbon/XML, would take me less than half the time in a form (and it would not have any issues refreshing).

The users really don't care about the ribbon interface, they just want a fast way to do what they need.

In conclusion, ... I agree that the Invalidate should work.
Perhaps it is just something simple we are all missing here...
Perhaps you can contact Cindy Meister directly and ask for explicit instructions:
http://homepage.swissonline.ch/cindymeister

JeffCoachman

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35023521
Hey boag,
I hope you don't mind I've been taken away from this discussion for a deadline - may I return Friday or Saturday of this question?

Also I get ALT - but what is FMC stand for?
An also FWIW?

When you put C & R - you are talking about compact and repair - yes?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35033003
In Access 2007 and newer the old keyboard shortcuts had to be extended
FMC stands for the hotkeys: File-->Manage-->Compact & Repair

As with the old hotkeys not all letters in the shortcut represent the first letter in the menu command.

For example in design view of a Form, do this to left align with a group of controls use this:
Alt+LL
Stands for: Arrange-->Left

If you hold down the Alt key you can see all the new shourtcuts on the ribbon.

FWIW stands for: (F)or (W)hat (I)t's (W)orth

Others you may see here:
IIRC=If I remember correctly
MYMV=Your Millage May Vary
GIYF=Google Is Your Friend

;-)

Jeff
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1332 total points
ID: 35033017
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

718 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