By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

How to Disable or Reroute Export to Excel Button in Ribbon?

P: 52
Hello,

I had a database user click on the "Export to Excel" button in the ribbon to try and export a report to excel when I already have a button on a report selector that runs VBA code I created to copy data into an excel templated report.

My question is how can I reroute the ribbon button to my code that works correctly? Or if that can't be done, how do I hide the button from all users?

Thanks in advance!
Oct 31 '11 #1

✓ answered by jbrumbau

Okay I solved it! It wasn't a context tab, it was in the main tabs. Here is my final layout:
Expand|Select|Wrap|Line Numbers
  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad">
  2.   <ribbon startFromScratch="false">
  3.     <tabs>
  4.       <tab idMso="TabHomeAccess" getVisible="AdminVisible" />
  5.       <tab id="jTabHomeAccess" visible="true" label="Home" >
  6.         <group id="jView" visible="true" label="View" >
  7.           <control idMso="ViewsFormView" visible="true" size="large" /> 
  8.           <control idMso="ViewsDatasheetView" visible="true" size="large" label="Spreadsheet View"/> 
  9.         </group>
  10.         <group idMso="GroupClipboard" visible="true" />
  11.         <group idMso="GroupSortAndFilter" visible="true" />
  12.         <group idMso="GroupRecords" visible="true" />
  13.         <group idMso="GroupFindAccess" visible="true" />
  14.       </tab>
  15.       <tab idMso="TabCreate" getVisible="AdminVisible" />
  16.       <tab idMso="TabExternalData" getVisible="AdminVisible" />
  17.       <tab idMso="TabDatabaseTools" getVisible="AdminVisible" />
  18.       <tab idMso="TabAddIns" getVisible="AdminVisible" />
  19.       <tab idMso="TabPrintPreviewAccess" >
  20.           <group idMso="GroupPrintPreviewData" visible="false" />
  21.       </tab>
  22.     </tabs>
  23.     <contextualTabs>
  24.       <tabSet idMso="TabSetTableToolsDatasheet" visible="false" />
  25.       <tabSet idMso="TabSetFormDatasheet" visible="false" />
  26.       <tabSet idMso="TabSetFormReportExtensibility">
  27.         <tab idMso="TabPrintPreviewAccess" >
  28.           <group idMso="GroupPrintPreviewData" visible="false" />
  29.         </tab>
  30.       </tabSet>
  31.     </contextualTabs>
  32.   </ribbon>
  33. </customUI>
  34.  

Share this Question
Share on Google+
14 Replies


patjones
Expert 100+
P: 931
I put all the functionality that the users need in the form(s) as well. I'm not sure if there's a way to keep the ribbon hidden, so as not to display it (I usually distribute front ends with it hidden, but users can easily show it again).

What I do is, if you go to the Microsoft button in the upper left hand corner, then Access Options > Current Database, you'll find all sorts of options for locking certain functionality out (full menus vs. limited, etc.) - maybe there's also something in there to keep the ribbon hidden, but I have not looked extensively for it.

Pat
Oct 31 '11 #2

patjones
Expert 100+
P: 931
When you uncheck "Allow Full Menus" and "Allow Default Shortcut Menus" most of the functionality in the ribbon is not available...including export/import operations.
Oct 31 '11 #3

P: 52
Unfortunately, unchecking "Allow Full Menus" is taking away the toggle from form view to datasheet view, which is something all users of our database use regularly. Is there a different solution? Any VBA code I could run that specifically disables the button?

Thanks.
Oct 31 '11 #4

patjones
Expert 100+
P: 931
There may well be VBA code that does this, but if so then it would probably involve various Windows system calls and a large amount of code to accomplish a pretty small thing. If switching between form view and datasheet view is the only that the end users go to the ribbon for, I'd still disable the ribbon functionality and try to find a VBA method for allowing the form/datasheet view transition.
Oct 31 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
Office applications all have CommandBars collections and each CommandBar has a CommandBarControls collection. Thus you could find what you want to disable and disable it. Whether this saves the changes for all databases is another matter though. You may have to handle enabling / disabling it on the open and close of your database (Not 100% reliable as you can reset the project while developing and therefore miss the code that resets it to how it was found).
Nov 1 '11 #6

P: 52
Hi NeoPa,

Thanks for your reply. I tested what you said, I did a loop through all CommandBar objects and determined the following ones could be what I'm interested in disabling:
Expand|Select|Wrap|Line Numbers
  1. CommandBars(191) '.Name = "Export Pop-Up"
  2. CommandBars(192) '.Name = "Import/Link Pop-Up"
  3. CommandBars(209) '.Name = "Ribbon"
  4.  
However, upon setting any of the above's Enabled property to False, the ribbon remains unchanged. I then tried this to the controls of the command bars:
Expand|Select|Wrap|Line Numbers
  1. For i = 1 To 11
  2.     CommandBars(191).Controls(i).Enabled = False
  3. Next i
  4.  
This has no effect on the ribbon export items either. Menus still pop up when I click on them. Am I doing something wrong or is there really no way to disable these buttons?

Thanks.
Nov 1 '11 #7

P: 52
Also I'd like to add that CommandBars(209), the ribbon one, only has 1 control in it, which is also called ribbon. Disabling it likewise had no effect.
Nov 1 '11 #8

P: 25
make a table called USysRibbons
with two fields
RibbonName Text key field
RibbonXML Memo

Need one record and in memo field place xml code
Expand|Select|Wrap|Line Numbers
  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <ribbon startFromScratch="true">
  3. </ribbon>
  4. </customUI>
The table will be hidden unless you have 'show system objects' checked for navigation options
Set access options to start with your ribbon.
You will have to close and open database for ribbon name to show up

This will eliminate all ribbons.
See for more help
http://www.accessribbon.de/en/?Access_-_Ribbons
Nov 2 '11 #9

NeoPa
Expert Mod 15k+
P: 31,709
Sorry to take so long to get back to you on this, but could you just pop in an example of the code you used to try to disable one of the CommandBar objects. I need to do some further testing, and I only have 2003 available, but I need not just how I would do it but also to compare that with what you're doing (I have an example from you already for the CommandBarControl code).
Nov 2 '11 #10

P: 52
Hennepin,

Thanks for your reply. I don't want to eliminate all ribbons, as nearly everything on the Home tab would be used by database users. Could you please let me know what XML code to use to just include the Home tab?

Searching around the internet some more I found a list of all ribbon commands:
http://www.microsoft.com/download/en...ang=en&id=3582

NeoPa,
If you're just using Access 2003 then I don't think you'll be able to test out ribbon related issues. As for the code I employ for my Switchboard form which opens automatically when the database loads:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. '...Irrelevent code omitted
  3.     Call DisableExportButtons
  4. '...Irrelevent code omitted
  5. End Sub
  6.  
  7. Private Sub DisableExportButtons()
  8.     Dim tempstr As String
  9.     Dim i As Integer
  10.  
  11. On Error GoTo Err_Handler
  12.     tempstr = ""
  13.     If Application.Version >= 12 Then   'Disable the Export to Excel button on the ribbon as this is the wrong one
  14.         CommandBars(191).enabled = False    'Export Pop-Up
  15.         CommandBars(192).enabled = False    'Import/Link Pop-Up
  16.         CommandBars(209).enabled = True     'Ribbon (only holds 1 control, odd)
  17.         For i = 1 To 11
  18.             'If InStrB(1, CommandBars(i).Name, "imp", vbTextCompare) Then
  19.             '    tempstr = tempstr & CommandBars(i).Name & " (" & i & ")" & Chr(13)
  20.             'End If
  21.             CommandBars(191).Controls(i).enabled = False
  22.  
  23.         '    tempstr = tempstr & CommandBars(209).Controls(i).caption & " (" & i & ")" & Chr(13)
  24.         Next i
  25.         'MsgBox tempstr
  26.     End If
  27. Exit_Handler:
  28.     Exit Sub '
  29.  
  30. Err_Handler:
  31.     Call MsgBox("Subscript out of range, i = " & i, vbExclamation, "Index Range Exceeded")
  32.     Resume Exit_Handler
  33. End Sub
  34.  
Anyway let me know. Thanks.
Nov 2 '11 #11

P: 52
Aha, I figured it out! Thanks Hennepin for pointing me in the right direction. I found a Microsoft article here and was able to solve it:
http://office.microsoft.com/en-us/ac...010211415.aspx

I created a table with the following items:
Table Name [USysRibbons]
ID - Autonumber
RibbonName - Text, 255 characters
RibbonXml - Memo

For record 1, I entered:
RibbonName=My Tab
RibbonXml=
Expand|Select|Wrap|Line Numbers
  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2.   <ribbon startFromScratch="false">
  3.     <tabs>
  4.       <tab idMso="TabCreate" visible="false" />
  5.       <tab idMso="TabExternalData" visible="false" />
  6.       <tab idMso="TabDatabaseTools" visible="false" />
  7.       <tab idMso="TabAddIns" visible="false" />
  8.     </tabs>
  9.   </ribbon>
  10. </customUI>
  11.  
Then I went to File, Options, Current Database, then for Ribbon Name, selected "My Tab". Then I restarted and it is now correct!

Thanks for all your help!
Nov 2 '11 #12

patjones
Expert 100+
P: 931
Wow, this is great to know. I'm definitely bookmarking this one for future reference! Thanks for the information.
Nov 2 '11 #13

P: 52
Ok so I built this thing up some more, I've disabled design view in the home tab by essentially rebuilding it. It turns out I celebrated a bit too soon. Upon opening a report, I noticed the pesky Excel export button appeared again, this time in a "Print Preview" tab in the "Data" group. I want this group to go away. Here is my current XML code:
Expand|Select|Wrap|Line Numbers
  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad">
  2.   <ribbon startFromScratch="false">
  3.     <tabs>
  4.       <tab idMso="TabHomeAccess" getVisible="AdminVisible" />
  5.       <tab id="jTabHomeAccess" visible="true" label="Home" >
  6.         <group id="jView" visible="true" label="View" >
  7.           <control idMso="ViewsFormView" visible="true" size="large" /> 
  8.           <control idMso="ViewsDatasheetView" visible="true" size="large" label="Spreadsheet View"/> 
  9.         </group>
  10.         <group idMso="GroupClipboard" visible="true" />
  11.         <group idMso="GroupSortAndFilter" visible="true" />
  12.         <group idMso="GroupRecords" visible="true" />
  13.         <group idMso="GroupFindAccess" visible="true" />
  14.       </tab>
  15.       <tab idMso="TabCreate" getVisible="AdminVisible" />
  16.       <tab idMso="TabExternalData" getVisible="AdminVisible" />
  17.       <tab idMso="TabDatabaseTools" getVisible="AdminVisible" />
  18.       <tab idMso="TabAddIns" getVisible="AdminVisible" />
  19.     </tabs>
  20.     <contextualTabs>
  21.       <tabSet idMso="TabSetTableToolsDatasheet" visible="false" />
  22.       <tabSet idMso="TabSetFormDatasheet" visible="false" />
  23.       <tabSet idMso="TabPrintPreviewAccess" visible="false" />
  24.     </contextualTabs>
  25.   </ribbon>
  26. </customUI>
  27.  
Note that setting visibility of TabPrintPreviewAccess to false does nothing. Anybody know what the proper reference for the aforementioned tab and group is???
Nov 3 '11 #14

P: 52
Okay I solved it! It wasn't a context tab, it was in the main tabs. Here is my final layout:
Expand|Select|Wrap|Line Numbers
  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad">
  2.   <ribbon startFromScratch="false">
  3.     <tabs>
  4.       <tab idMso="TabHomeAccess" getVisible="AdminVisible" />
  5.       <tab id="jTabHomeAccess" visible="true" label="Home" >
  6.         <group id="jView" visible="true" label="View" >
  7.           <control idMso="ViewsFormView" visible="true" size="large" /> 
  8.           <control idMso="ViewsDatasheetView" visible="true" size="large" label="Spreadsheet View"/> 
  9.         </group>
  10.         <group idMso="GroupClipboard" visible="true" />
  11.         <group idMso="GroupSortAndFilter" visible="true" />
  12.         <group idMso="GroupRecords" visible="true" />
  13.         <group idMso="GroupFindAccess" visible="true" />
  14.       </tab>
  15.       <tab idMso="TabCreate" getVisible="AdminVisible" />
  16.       <tab idMso="TabExternalData" getVisible="AdminVisible" />
  17.       <tab idMso="TabDatabaseTools" getVisible="AdminVisible" />
  18.       <tab idMso="TabAddIns" getVisible="AdminVisible" />
  19.       <tab idMso="TabPrintPreviewAccess" >
  20.           <group idMso="GroupPrintPreviewData" visible="false" />
  21.       </tab>
  22.     </tabs>
  23.     <contextualTabs>
  24.       <tabSet idMso="TabSetTableToolsDatasheet" visible="false" />
  25.       <tabSet idMso="TabSetFormDatasheet" visible="false" />
  26.       <tabSet idMso="TabSetFormReportExtensibility">
  27.         <tab idMso="TabPrintPreviewAccess" >
  28.           <group idMso="GroupPrintPreviewData" visible="false" />
  29.         </tab>
  30.       </tabSet>
  31.     </contextualTabs>
  32.   </ribbon>
  33. </customUI>
  34.  
Nov 3 '11 #15

Post your reply

Sign in to post your reply or Sign up for a free account.