You only have to run the code once to protect the command bars. You might want to simply run this before you distribute the application. Or, you might want to provide a mechanism whereby users can protect and unprotect the items at will, making it easier to control the behavior.

To work with command bars programmatically, you must first select the Tools > References menu from VBA to set a reference to the Microsoft Office <xx> Object Library, where <xx> corresponds to the version of Office you're using. Then, create a standard module, and add these two procedures:

Sub ProtectCommandBars()
Dim cbar As CommandBar

For Each cbar In Application.CommandBars
If Not cbar.BuiltIn Then
cbar.Protection = _
msoBarNoMove Or msoBarNoCustomize Or _
msoBarNoChangeVisible
End If
Next cbar
End Sub

Sub UnprotectCommandBars()
Dim cbar As CommandBar

For Each cbar In Application.CommandBars
If Not cbar.BuiltIn Then
cbar.Protection = msoBarNoProtection
End If
Next cbar
End Sub

Then, to protect your command bars, call the ProtectCommandBars method; to unprotect, call UnprotectCommandBars. If you only want to do this once, you can call either method from the Immediate window. If you want to let users make the choice whether to protect the command bars, call the procedures from menu items. In any case, I've found these procedures useful in ensuring my applications work the way I intended.

-- Willem Holleman, Leiden, The Netherlands

I've fought with command bars for years, and never noticed this feature! Thanks for pointing it out. Being compulsive about reducing code size, however, I feel compelled to provide a version of the code that merges the two procedures. I'm not sure it's any fewer lines of code, but it does let you provide the same functionality without having to maintain two separate procedures.

Here's my slightly modified version:

Sub HandleCommandBars(Protect As Boolean)
Dim cbar As CommandBar
Dim value As MsoBarProtection

' Calculate the value to store into the
' Protection property:
If Protect Then
value = msoBarNoMove Or _
msoBarNoCustomize Or msoBarNoChangeVisible
Else
value = msoBarNoProtection
End If
' Now loop through all the command bars,
' looking for ones that aren't built in.
For Each cbar In Application.CommandBars
If Not cbar.BuiltIn Then
cbar.Protection = value
End If
Next cbar
End Sub

Call the procedure passing in True or False to protect or unprotect your command bars:

' Protect:
HandleCommandBars True

' Unprotect:
HandleCommandBars False

Whether you go with the original procedures or this combined procedure, you can finally keep users from undermining your careful work.

-- Ken Getz, Access-VB-SQL Advisor Magazine technical editor

This tip was published in the December 2004 issue of ACCESS ADVISOR Magazine. Subscribers can get more Advisor Tips, Advisor Answers, and how-to advice on succeeding with Microsoft Access technology at www.MSAccessAdvisor.com.