Question Import a 2003 CommandBar into 2007

[harpyopsis]

Dear all !

I am just migrating a 2003 *.mdb to 2007 *.accmdb (then to 2016).

None of my users is fan of the Ribbon and myself very reluctant to spend time in managing XML USysRibbons.

I built an Access 2003 CommandBar with 13 controls and some 60 Items (Text Only always), very easy to enable / disable according to the users’ permission levels.

DoCmd.ShowToolBar "Menu Bar", acToolbarYes
      
  Dim MyCmdBar As CommandBar, TargetCBItem As CommandBarControl
      Set MyCmdBar = CommandBars("VetoBar")
      
  MyCmdBar.Controls(11).Enabled = True                ‘Accounting 
  Set TargetCBItem = CommandBars.FindControl(Tag:=" CptaTVAVent ")
      If Not TargetCBItem Is Nothing Then TargetCBItem.Enabled = True

Sober and compact, this CommandBar gave us full satisfaction over many years.

I just discover the « Classic Menu for Office » on AddinTools, which brings the 2003 original “Menu Bar” + ToolBox inside a 2007 Ribbon Tab which is automatically named “Menus”.

After referencing OFFICE\MSO.dll, I could also successfully import my old “VetoBar” which works perfectly under 2007.

The reason I call you now is because this CommandBar is oddly included inside a custom Tab of the Ribbon automatically called “Add-Ins”. It does NOT replace the ribbon. Ribbon which again brings its one-inch height as an empty space on top of the display...

See attached images

Would any one have a hint on how to start optimizing the top of the 2007 Access window, knowing that we do not need any cumbersome ribbon-like structure ????

Thank you !


phil

 

[theDBguy]

Hi phil,

Unfortunately, the space on top for the Ribbon is occupied by the Ribbon. It is either used, or it’s not. There’s no way, I am aware of to only use some of that space. The closest I think you can do is to maybe use a simple custom Ribbon to only display the Add-Ins tab.

 

[isladogs]

Welcome to the forum.
Your post was moderated due to the two attached images.
New users can only attach files if these are zipped.

What you have experienced, with a command bar being listed under add-ins, is how Access always deals with these when updating to ACCDB.
I don't think there is an easy solution, other than converting to a ribbon or using external tools to emulate the old style toolbars and menus

EDIT
Forgot to say that the ribbon can be minimised. Very easy to do in A2010 or later.
More difficult in A2007.
I can supply code to do that for either version if that helps.
Or you can remove the ribbon completely.

 

[harpyopsis]

Thank you very much DBGuy !

I will try this tomorrow and keep you informed (it is 00.45 over here) : I'll try to create an empty ribbon from scratch, just to hold the commandBar inside a single Add-ins tab... Too funny ! Out of curiosity to see whether that ribbon will mind to adapt its height to the CommandBar's height...
I cannot see myself XML enabling / disabling 60 buttons inside a ribbon according to our users various permissions...

 

[harpyopsis]

Hello Colin ! Thank you for your post. What kind of external tools do you have in mind that can emulate a CommandBar in place of a bulky ribbon ?

 

[harpyopsis]

Colin I am sure very interested in this code !!!

 

[isladogs]

Your last 3 posts also got moderated - not sure why.

1. External Tools - I meant things like Classic Menu which you already mentioned.
I used it for a while when A2007 came out but eventually began to appreciate the ribbon & binned it.

2. Code to manage the ribbon. Place in a standard module
The ToggleRibbonState function doesn't work in A2007.
From memory it causes errors.

Public Function HideRibbon()
    'could run at startup using Autoexec
    'however this also hides the QAT which makes printing reports tricky
     DoCmd.ShowToolbar "Ribbon", acToolbarNo
   '  DoCmd.ShowToolbar "PrintReport", acToolbarYes
End Function

Public Function ShowRibbon()
    'use when opening a report to display print preview ribbon
     DoCmd.ShowToolbar "Ribbon", acToolbarYes
End Function

Public Function ToggleRibbonState()

If GetAccessVersion > 12 Then
    'hide ribbon if visible & vice versa
    'doesn't work in Access 2007
    CommandBars.ExecuteMso "MinimizeRibbon"
End If

End Function

Public Function IsRibbonMinimized() As Boolean
    'Result: 0=normal (maximized), -1=autohide (minimized)

    IsRibbonMinimized = (CommandBars("Ribbon").Controls(1).Height < 100)
   ' Debug.Print IsRibbonMinimized
End Function

3. Alternative and much longer code (not mine) for use in A2007

Option Compare Database
Option Explicit

Declare Function SetForegroundWindow Lib "user32.dll" ( _
                 ByVal hWnd As Long) As Long
                 
Declare Function SetActiveWindow Lib "user32.dll" ( _
                 ByVal hWnd As Long) As Long
                 
Private Declare Function apiSetFocus Lib "user32.dll" Alias "SetFocus" ( _
                ByVal hWnd As Long) As Long

Function RibbonState() As Long
    'Result: 0=normal, -1=autohide
    RibbonState = (CommandBars("Ribbon").Controls(1).Height < 100)
End Function
Function MaximizeRibbon(Optional TimeOut As Long = 2) As Boolean
    Dim T As Single
    MaximizeRibbon = True
    If RibbonState = 0 Then Exit Function
    T = Timer()
    'Exit from loop if ribbon is finally maximized
    ' or we have a timeout of 2 seconds - this seems to be sufficient.
   '(Cause: Ribbon does not always react on SendKeys)
    Do While (RibbonState = -1) And (Timer - T) < TimeOut
        SetForegroundWindow Application.hWndAccessApp
        SetActiveWindow Application.hWndAccessApp
        apiSetFocus Application.hWndAccessApp
        SendKeys "^{F1}"    ' Ctrl+F1
        ' SendKeysAPI "{^F1}"
        DoEvents
    Loop
    MaximizeRibbon = (Timer - T) < TimeOut
End Function
Function MinimizeRibbon(Optional TimeOut As Long = 2) As Boolean
    Dim T As Single
    MinimizeRibbon = True
    If RibbonState = -1 Then Exit Function
    T = Timer()
    Do While (RibbonState = 0) And (Timer - T) < TimeOut
        SetForegroundWindow Application.hWndAccessApp
        SetActiveWindow Application.hWndAccessApp
        apiSetFocus Application.hWndAccessApp
        SendKeys "^{F1}"    ' Ctrl+F1
        ' SendKeysAPI "{^F1}"
        DoEvents
    Loop
    MinimizeRibbon = (Timer - T) < TimeOut
End Function

'= = = = = END OF MODULE CODE = = = = =

'---code for forms
Option Compare Database
Option Explicit

Private Sub Form_Activate()
    
    MinimizeRibbon       '  Minimize 2007 Ribbon . so more space on screen for form
End Sub

'-----code for reports
Option Compare Database
Option Explicit
Private Sub Report_Activate()
    MaximizeRibbon  ' to display ribbon . for printer orientation/layout options for the report being displayed
    
End Sub

Note that the above uses API declarations. If any users have 64-bit Access in A2010 or later these will need modifying before using.

4. You could look at this example database on my website for ideas on how to use Access without a ribbon / nav pane / application window.
See http://www.mendipdatasystems.co.uk/control-application-interface/4594365418