Question Hide/show Ribbon Tabs/Areas

[Jalex]

Hi all!

I think this should be easy, but need some guidance =/

Scenario

The user wants all the non-basic functionality of the ribbon hiden. To accomplish that by doing the usual stuff:

File >> Options >> Current Database >> Allow Full Menus (unchecked)

Everything worked as expected, but we came across an issue. The user needs to open a report in "print preview" and they need to be able to export their report to excel. The "print preview" ribbon tab is there, but it is not showing the "Data" area, where all the export options are.

How can I do this without a costume ribbon?

Maybe there is a way of manipulating which areas of the ribbon/tabs/areas/controls are enabled from VBA? That would be a great solution.

Thanks a lot!

 

[Ari]

Hi

An alternative would be to use the F5 shortcut key to invoke the export form.

Use the keyDown event :

example:

Private Sub Report_KeyDown (KeyCode As Integer, Shift As Integer)
If KeyCode = 116 Then DoCmd.RunCommand acCmdExportExcel
end Sub

 

[Jalex]

Hi

An alternative would be to use the F5 shortcut key to invoke the export form.

Use the keyDown event :

example:

Private Sub Report_KeyDown (KeyCode As Integer, Shift As Integer)
If KeyCode = 116 Then DoCmd.RunCommand acCmdExportExcel
end Sub

That's a nice workaround.

Thus adding it to the F5 key makes it throw an annoying "Filter not allowed" message box. I added this code though:

Private Sub Report_KeyDown (KeyCode As Integer, Shift As Integer)
If KeyCode = 116 Then
DoCmd.RunCommand acCmdExportExcel
KeyCOde = 0
End If
End Sub

Now, the problem is that it is not exporting.... :banghead:

You press OK and Access just stays there idle. If you do it from the ribbon (Print Preview >> Data >> Export To Excel), after clicking OK the wizard shows the "printing window", then sends you to the "save steps" form.

Any thoughts?

 

[boblarson]

Which version of Access? In 2007 there aren't programmatical ways to customize the Ribbon. In 2010 there are.

 

[Jalex]

Which version of Access? In 2007 there aren't programmatical ways to customize the Ribbon. In 2010 there are.

Access 2010 14.0.6112.5 (32-bit)

 

[speakers_86]

Care to go on Bob? I want to know.

 

[boblarson]

Care to go on Bob? I want to know.

If you look, you can find anything.
Google is your friend.

Start here:
http://www.accessribbon.de/en/index.php?New_Features_in_Access_2010

I have not worked with them but we were told at some point by the Access Team that there were more programmable elements in 2010 than there were in 2007 for the Ribbon.

 

[speakers_86]

I've been up down and across that site.

The only methods for the IRibbonUI object are a few activate tab or mso objects. What you said made it sound like you could change the ribbon with vba, as opposed to xml, that's why I was interested.

 

[Ari]

Hi Jalex

Use the macro Autokeys, to trigger the F5 key

{f5} | [CurrentProject].[AllReports](Reports (0).[Name]).[IsLoaded] | RunCommand: ExportExcel

You may need to add the treatment of error, if you have not loaded a report.

 

[Jalex]

Hi Jalex

Use the macro Autokeys, to trigger the F5 key

{f5} | [CurrentProject].[AllReports](Reports (0).[Name]).[IsLoaded] | RunCommand: ExportExcel

You may need to add the treatment of error, if you have not loaded a report.

After an hour of trying to make it work, I got stuck in calling the code from the Autokeys macro: "The expression you entered has a function name that .... can't find".

The procedure is in a module, it is public, no spelling mistake, upcase/lowcase match, parenthesis in there.

Public Sub ExportToExcel()
If [CurrentProject].[AllReports](Reports(0).[ReportName]).[IsLoaded] Then
DoCmd.RunCommand acCmdExportExcel
End If
End Sub


Macro: Autokeys >> Submacro +{F9} Run Code Function Name ExportToExcel()

What am I missing?

 

[Ari]

Hi

Macro does not work with procedure (sub). Only with function.

Change to:

Public Function ExportToExcel()
On Error Resume Next
If Len(Reports(0).Name & "") > 0 Then
    If Err Then
        Err.Clear
    Else
        DoCmd.RunCommand acCmdExportExcel
    End If
End If
End Function

 

[Jalex]

Hi

Macro does not work with procedure (sub). Only with function.

Change to:

Public Function ExportToExcel()
On Error Resume Next
If Len(Reports(0).Name & "") > 0 Then
    If Err Then
        Err.Clear
    Else
        DoCmd.RunCommand acCmdExportExcel
    End If
End If
End Function

Yes! Such a newbie with macros.

Wow, Ari, thanks a lot, saved my life completely. Owe you one.