Right-click CommandBar Controls Enabled and Grayed-out

[duggie33]

I have a custom CommandBar with six Controls and I selectively enable (Enabled=True) and disable (Enabled=False) depending on the worksheet cells that the user right-clicks on.

The creation of the CommandBar and Controls happens on Workbook Open and the enabling and disabling of the Controls occurs on the BeforeRightClick Event of each Worksheet.

There are six Worksheets and everything works as expected on all but one. The BeforeRightClick Event fires, all controls are disabled (Enabled=False), and then only the controls needed for the cell right-clicked are enabled. A Debug.Print has confirmed that the correct Controls are enabled. The problem is that the enabled controls are often grayed-out. On this one Worksheet, the Controls seem to keep the grayed-out state that they were from the last (different) Worksheet in which the user right-clicked on a cell.

I have been through this code so many times I lost count; looking for something wrong but cannot find any reason for this behavior. This is a work related project so I cannot share it, but I will share some code snippets in hopes someone has gone through this and found a solution.

This is used to disable the Controls:

    Dim i As Integer
    For i = 1 To Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls.Count
        Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(i).Enabled = False
    Next i

Used to enable Control if cell is within named range:

    If Not Intersect(Target, shJobDetails.Range("POonJobDetails")) Is Nothing Then
        Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(3).Enabled = True
    End If

Used to check status of controls for debugging:

    Debug.Print Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(1).Enabled
    Debug.Print Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(2).Enabled
    Debug.Print Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(3).Enabled
    Debug.Print Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(4).Enabled
    Debug.Print Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(5).Enabled
    Debug.Print Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(6).Enabled

Does anyone have any ideas on why the incorrect Controls would be grayed-out?

Many thanks in advance for considering this...

Doug

 

[duggie33]

I have discovered how to get this to work.

By changing from:

Application.CommandBars("List Range PopUp").Controls("Epicor Lookups").Controls(3).Enabled = True

To this:

Application.CommandBars("Epicor Lookups").Controls(3).Enabled = True

On some sheets, it works with the original code and on others with the revised code.

Doug