Solved

How to refresh changes to the ribbon?

Posted on 2011-03-01
17
3,357 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
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 6

Accepted Solution

by:
Gugro earned 167 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:stephenlecomptejr
Comment Utility
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
Comment Utility
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
Comment Utility
Did you try running the Compact/Repair utility?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
<but I can't get to the tables.> which table? systems table?

office button> access options >current database >Navigations options > show system objects
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:stephenlecomptejr
Comment Utility
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
Comment Utility
Hey caps, was that for another question and this by accident?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
sorry, wrong thread....
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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
Comment Utility
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 333 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 333 total points
Comment Utility
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now