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

[jbrumbau]

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!

[patjones]

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

[patjones]

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.

[jbrumbau]

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.

[patjones]

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.

[NeoPa]

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).

[jbrumbau]

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:

CommandBars(191) '.Name = "Export Pop-Up"
CommandBars(192) '.Name = "Import/Link Pop-Up"
CommandBars(209) '.Name = "Ribbon"
 

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:

For i = 1 To 11
    CommandBars(191).Controls(i).Enabled = False
Next i
 

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.

[jbrumbau]

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.

[Hennepin]

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

Need one record and in memo field place xml code

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
</ribbon>
</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

[NeoPa]

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).

[jbrumbau]

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:

Private Sub Form_Load()
'...Irrelevent code omitted
    Call DisableExportButtons
'...Irrelevent code omitted
End Sub
 
Private Sub DisableExportButtons()
    Dim tempstr As String
    Dim i As Integer
 
On Error GoTo Err_Handler
    tempstr = ""
    If Application.Version >= 12 Then   'Disable the Export to Excel button on the ribbon as this is the wrong one
        CommandBars(191).enabled = False    'Export Pop-Up
        CommandBars(192).enabled = False    'Import/Link Pop-Up
        CommandBars(209).enabled = True     'Ribbon (only holds 1 control, odd)
        For i = 1 To 11
            'If InStrB(1, CommandBars(i).Name, "imp", vbTextCompare) Then
            '    tempstr = tempstr & CommandBars(i).Name & " (" & i & ")" & Chr(13)
            'End If
            CommandBars(191).Controls(i).enabled = False
 
        '    tempstr = tempstr & CommandBars(209).Controls(i).caption & " (" & i & ")" & Chr(13)
        Next i
        'MsgBox tempstr
    End If
Exit_Handler:
    Exit Sub '
 
Err_Handler:
    Call MsgBox("Subscript out of range, i = " & i, vbExclamation, "Index Range Exceeded")
    Resume Exit_Handler
End Sub
 

Anyway let me know. Thanks.

[jbrumbau]

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=

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab idMso="TabCreate" visible="false" />
      <tab idMso="TabExternalData" visible="false" />
      <tab idMso="TabDatabaseTools" visible="false" />
      <tab idMso="TabAddIns" visible="false" />
    </tabs>
  </ribbon>
</customUI>
 

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!

[patjones]

Wow, this is great to know. I'm definitely bookmarking this one for future reference! Thanks for the information.

[jbrumbau]

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:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad">
  <ribbon startFromScratch="false">
    <tabs>
      <tab idMso="TabHomeAccess" getVisible="AdminVisible" />
      <tab id="jTabHomeAccess" visible="true" label="Home" >
        <group id="jView" visible="true" label="View" >
          <control idMso="ViewsFormView" visible="true" size="large" /> 
          <control idMso="ViewsDatasheetView" visible="true" size="large" label="Spreadsheet View"/> 
        </group>
        <group idMso="GroupClipboard" visible="true" />
        <group idMso="GroupSortAndFilter" visible="true" />
        <group idMso="GroupRecords" visible="true" />
        <group idMso="GroupFindAccess" visible="true" />
      </tab>
      <tab idMso="TabCreate" getVisible="AdminVisible" />
      <tab idMso="TabExternalData" getVisible="AdminVisible" />
      <tab idMso="TabDatabaseTools" getVisible="AdminVisible" />
      <tab idMso="TabAddIns" getVisible="AdminVisible" />
    </tabs>
    <contextualTabs>
      <tabSet idMso="TabSetTableToolsDatasheet" visible="false" />
      <tabSet idMso="TabSetFormDatasheet" visible="false" />
      <tabSet idMso="TabPrintPreviewAccess" visible="false" />
    </contextualTabs>
  </ribbon>
</customUI>
 

Note that setting visibility of TabPrintPreviewAccess to false does nothing. Anybody know what the proper reference for the aforementioned tab and group is???

[jbrumbau]

Okay I solved it! It wasn't a context tab, it was in the main tabs. Here is my final layout:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad">
  <ribbon startFromScratch="false">
    <tabs>
      <tab idMso="TabHomeAccess" getVisible="AdminVisible" />
      <tab id="jTabHomeAccess" visible="true" label="Home" >
        <group id="jView" visible="true" label="View" >
          <control idMso="ViewsFormView" visible="true" size="large" /> 
          <control idMso="ViewsDatasheetView" visible="true" size="large" label="Spreadsheet View"/> 
        </group>
        <group idMso="GroupClipboard" visible="true" />
        <group idMso="GroupSortAndFilter" visible="true" />
        <group idMso="GroupRecords" visible="true" />
        <group idMso="GroupFindAccess" visible="true" />
      </tab>
      <tab idMso="TabCreate" getVisible="AdminVisible" />
      <tab idMso="TabExternalData" getVisible="AdminVisible" />
      <tab idMso="TabDatabaseTools" getVisible="AdminVisible" />
      <tab idMso="TabAddIns" getVisible="AdminVisible" />
      <tab idMso="TabPrintPreviewAccess" >
          <group idMso="GroupPrintPreviewData" visible="false" />
      </tab>
    </tabs>
    <contextualTabs>
      <tabSet idMso="TabSetTableToolsDatasheet" visible="false" />
      <tabSet idMso="TabSetFormDatasheet" visible="false" />
      <tabSet idMso="TabSetFormReportExtensibility">
        <tab idMso="TabPrintPreviewAccess" >
          <group idMso="GroupPrintPreviewData" visible="false" />
        </tab>
      </tabSet>
    </contextualTabs>
  </ribbon>
</customUI>