Custom Ribbons Using CommandBars with Adrian Bell

Ever wondered how to create dynamic ribbon modifications in Access without dealing with complex XML?

In this Access Europe user group presentation, Adrian Bell demonstrates how to leverage the legacy CommandBars technology to create custom ribbon elements that can be modified on the fly. The talk focuses on using CommandBars as a simpler alternative to XML-based ribbon customization, particularly useful for dynamically adding and removing toolbar buttons during runtime.

This detailed breakdown offers practical insights for Access developers looking to enhance their applications with flexible toolbar customizations, complete with working examples and reusable code that you can implement immediately.

Background and Context

Legacy Technology with Modern Applications

• CommandBars predate the ribbon interface (pre-2007)
• Still supported in modern versions of Access
• Appears automatically in the "Add-ins" tab
• Provides runtime flexibility not available with XML ribbons

Key Benefits

• No XML knowledge required
• Changes take effect immediately without restarting Access
• Can be temporary or permanent
• Simpler implementation than traditional ribbon customization

Technical Implementation

Basic Structure

• CommandBars (CBs) - The container objects
• CommandBar Controls (CBCs) - Individual elements
• CommandBar Buttons (CBBs) - Most commonly used control type

Core Functionality

• Create/delete command bars
• Show/hide command bars
• Add/remove command bar controls
• Manage button actions and appearance

Code Organization

• Class module for command bar management
• Properties for accessing command bar objects
• Methods for common operations (create, delete, show, hide)

Working with CommandBar Buttons

Button Properties

• FaceID - Controls button icon (using built-in Office icons)
• Caption - Button text
• OnAction - Code to execute when clicked
• ToolTipText - Hover text

Action Handling

• Supports both functions and subroutines
• Functions can accept parameters
• Must use public procedures
• Limited to current database (unlike Excel)

Layout and Presentation

Visual Organization

• CommandBars arrange vertically
• Controls arrange horizontally within bars
• Maximum of three columns
• Automatic width adjustment based on content

Style Options

• Icon only
• Caption only
• Icon and caption combined
• Limited size options (small icons only)

Practical Applications

External Program Launch

• Start Windows applications
• Open system utilities
• Launch custom tools
• Integration with other software

Temporary vs Permanent

• Temporary bars removed on database close
• Permanent bars persist between sessions
• Useful for development tools
• Can be managed programmatically

Limitations and Considerations

Known Restrictions

• Cannot create custom groups in Add-ins tab
• Limited control over button sizing
• No support for large icons
• Some legacy features not supported in ribbon context

Best Practices

• Use temporary bars for runtime modifications
• Clean up bars when no longer needed
• Consider user screen resolution
• Test with various Office versions

Resources and References

Available Tools

• Face ID reference lists
• Sample databases with working examples
• Reusable code modules
• Implementation examples

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

• Interact directly with presenters during Q&A sessions
• Network with other Access developers
• Share your own experiences and challenges
• Get immediate answers to your specific questions
• Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

• Base cover image generated by FLUX-schnell
• Initial draft generated by Claude-3.5-Sonnet