VBA - Method 'List' of objectr '_CommandBarComboBox' failed

Question

I have a sub in VBA that calls another sub to perform some analysis on data if the user pastes it in. The code below errors as:

Run-time error'-2147467259 (80004005)': Method 'List' of object'_CommandBarComboBox' failed

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim UndoList As String
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)  'Errors Here!

    If Left(UndoList, 5) = "Paste" Then
        Dim Annotations() As String
        FindAnnots
    End If

End Sub

Any idea as to why the object's list doesn't exist?

Kirovabad

its working at my end – Concertmaster

Thanks Karthick. Like I said below in my answer, the .List property of Controls("&Undo") depends on actions being stored in the Undo list. If you haven't done anything worthy of being stored there, then it will not work. – Kirovabad

Answer 1

After using Bryan's answer about it being a CommandBarComboBox, I think you can check its enabled status to see if a list exists. Its the only way I've got it to work without using On Error Resume Next.

Use the following loop to capture the status of the ComboBox

Dim UndoList As String

If Application.CommandBars("Standard").Controls("&Undo").Enabled = True Then

    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

    If Left(UndoList, 5) = "Paste" Then

        'Code to run here after paste action'

    End If

End If

Disendow

Answer 2

Short Answer: The .List isn't there yet because the user hasn't performed any actions that get saved in the Undo queue. The code is trying to access something that hasn't been created yet. I had to create an error handler for it.

Long Answer: In my search, I found that the Controls object in VBA doesn't officially have a List property. However, Controls("&Undo") Is not a Controls object. It's a CommandBarComboBox.

So in

Application.CommandBars("Standard").Controls("&Undo").List(1)

The .List property of Controls("&Undo") doesn't actually show up in the Excel VBA intellisense. It's looking at the Controls object for it's intellisense drop-down. However, if you try

? TypeName(Application.CommandBars("Standard").Controls("&Undo"))

In the immediate window, you'll see that it's of the type CommandBarComboBox, which does have a list property. However, like other ComboBox style controls, no list is created until a list member is added. In this case, when the user performs an action worthy of being stored in the Undo queue.

Kirovabad