how to list commandbarcontrol properties (1 Viewer)

[CJ_London]

I'm trying to create a routine to list all the properties and values used by commandbarcontrols (there are 5 types of control, many common properties and some unique to certain types. In total around 44 properties) in a commandbar.

Unfortunately commandbarcontrols don't have a properties 'property' so code like

Dim prp As Property
        For Each prp In cbc.properties
            Debug.Print prp.Name; prp.Value
        Next prp

won't work. You get the error 'Object doesn't support this property or method

I've also tried using cbc(0) and variations thereof with the same error

And I've tried declaring cbc as a specific type of commandbarcontrol (e.g. button, combo or popup) - same error

At the moment I can only achieve what I want by listing all properties and excluding those that aren't valid with error management e.g.

debug.print "Index"; cbc.index
debug.print "BeginGroup"; cbc.begingroup
debug.print "Caption"; cbc.caption
etc

Note there isn't as Name property

which just seems to be long winded and requires prior knowledge of the various properties - I have the list so that is not the issue and is my fallback position if nothing else works. But just thought it was worth asking if anyone else has any ideas.

 

[Uncle Gizmo]

I noticed that you have yet to receive a reply to your question. I thought it might benefit from a bump as someone might have an inkling.

 

[gemma-the-husky]

I just wrote this to iterate the command bars which produced over 250 lines.

Sub showcommandbars()

Dim cbc As CommandBar
Dim s As String
Dim fno As Long
Dim fname As String


    s = ""
    For Each cbc In Application.CommandBars
        s = s & cbc.Name & vbCrLf
    Next

    fno = FreeFile

    fname = CurrentProject.path & "\" & "cbcs-" & Format(Now, "yyyy-mm-dd-hhnnss") & ".txt"
    Open fname For Output As #fno
    Print #fno, s
    Close #fno

    Application.FollowHyperlink fname
End Sub

I then tried to use a single command bar, but although intellisense brings up various methods and properties, I can't see a way to iterate any collection. My DAO reference doesn't refer to command bars or tool bars either.

I found this, but I haven't got time to research any further at the moment. Does this help at all?

CommandBar object (Office) | Microsoft Learn
CommandBar members (Office) | Microsoft Learn - I followed a link from the above to get to this

Note that the above code was written using A365, v16.0. I doubt A2003 would do any different though, as I think a ribbon must be a commandbar for this purpose also given the number of lines produced.

 

[CJ_London]

I'd actually given up on this thread . I came to conclusion there wasn't a hidden properties collection as you have with forms, controls etc

In the end I created a function and used a case statement to return the specified property value

Function getPropValue(cbc As Object, prpName As String) As Variant
    
    On Error Resume Next 'property not found
    Select Case prpName
        Case "Application"
            getPropValue = cbc.Application
        Case "BeginGroup"
            getPropValue = cbc.BeginGroup
        Case "BuiltIn"
            getPropValue = cbc.BuiltIn
        Case "BuiltInFace"
            getPropValue = cbc.BuiltInFace
        Case "Caption"
etc

 

[isladogs]

I missed this thread originally but, as I've already mentioned to Chris, I use Dale Fye's excellent Access Shortcut Tool add-in which gives full details of all built-in and custom command bars:

Access Shortcut (right-click) Tool – Developing Solutions

See also his series of related command bars articles on EE that are linked on the above web page