Cannot delete CommandBar Control

[Jon von der Heyden]

Hi All

Yet another little problem that I am struggling to solve.

I have a userform frmSetup. It has once command button and when clicked it is supposed to run routine 'BuildUI'.

'BuildUI' will also trigger another routine right at the start, callled 'ClearUI'. ClearUI is supposed to clear the controls in the Cell menu.

'BuildUI' runs fine if I trigger it manually. However, it produces an error when triggered by the commandbutton click on the form. It works the first time but subsequent attempts yields a 'Method Delete of Object Commandbar Button failed'. It will work if I manually trigger 'BuildUI' but not if triggered from the userform. Do you know why?

BuildUI

Sub BuildUI()
    Call ClearUI
'// Right click cell menu
    If shtControl.Range("B19") Then
        Call CreateControl("Consolidate Workbooks", "OpenMainForm", True)
        Call CreateControl("Setup Preferences", "OpenSetupForm", False)
    End If
End Sub

ClearUI

Sub ClearUI()
Dim cbcItem As CommandBarControl
Dim arrCbc()
'// Right click cell menu
    arrCbc = Array("Consolidate Workbooks", "Setup Preferences")
    For Each cbcItem In Application.CommandBars("Cell").Controls
        If Not IsError(Application.Match(cbcItem.Caption, arrCbc, 0)) Then
            [COLOR=red]cbcItem.Delete [B]'Error on this line[/B][/COLOR]
        End If
    Next cbcItem
End Sub

frmSetup:

Private Sub CommandButton1_Click()
 
    shtControl.Range("B19") = Me.CheckBox1.Value
    shtControl.Range("B20") = Me.CheckBox2.Value
    shtControl.Range("B21") = Me.CheckBox3.Value
    shtControl.Range("B22") = Me.CheckBox4.Value
    shtControl.Range("B23") = Me.CheckBox5.Value
 
    If Application.CountIf(shtControl.Range("B19:B23"), False) = 5 Then
        MsgBox "You need to choose at least one way of calling the wizard!", _
            vbExclamation, "Choose interface"
        Exit Sub
    End If
 
    Call BuildUI
    shtControl.Range("B25") = True
 
    Unload Me
 
End Sub

 

[Jon von der Heyden]

I'm still in a pickle over this. 'BuildUI' runs fine if I trigger 'OpenSetupForm' from my shortcut, or if I run the form directly from the VBE. But it still errors if I trigger it from my right-click cell menu.

Sub OpenMainForm()
    frmMain.Show
End Sub
Sub OpenSetupForm()
    frmSetup.Show
End Sub

Sub BuildUI()
    Call ClearUI
'// shortcuts
    If shtControl.Range("B23") Then
        Application.OnKey "^+C", "OpenMainForm"
        Application.OnKey "^+P", "OpenSetupForm"
    End If
    
'// Right click cell menu
    If shtControl.Range("B19") Then
        Call CreateControl("Consolidate Workbooks", "OpenMainForm", True)
        Call CreateControl("Setup Preferences", "OpenSetupForm", False)
    End If

End Sub

What I find bizarre is the error. Why would that error appear when the form is triggered from the cell menu, but run fine if triggered from the shortcut?

 

[Colin Legg]

Hi Jon,

When you click on the "Setup Preferences" commandbarcontrol to show the SetUp form, its OnAction method calls the OpenSetupForm procedure which shows the userform modally: code execution in that procedure is paused until the userform is unloaded. So while the userform is running the commandbarcontrol is effectively locked and you can't delete it - it has a method call still pending completion. Show the relevant userform modelessly and I think it will run OK.

Sub OpenSetupForm()
    frmSetUp.Show vbModeless
End Sub

Hope that helps...

 

[Jon von der Heyden]

Colin, that takes care of it thanks. But if you don't mind perhaps could you explain where my thinking is wrong?

So while the userform is running the commandbarcontrol is effectively locked and you can't delete it...

I had tried unloading before calling 'BuildUI' but I encountered the same. Why is that?

Private Sub CommandButton1_Click()
    
    shtControl.Range("B19") = Me.CheckBox1.Value
    shtControl.Range("B20") = Me.CheckBox2.Value
    shtControl.Range("B21") = Me.CheckBox3.Value
    shtControl.Range("B22") = Me.CheckBox4.Value
    shtControl.Range("B23") = Me.CheckBox5.Value
    
    If Application.CountIf(shtControl.Range("B19:B23"), False) = 5 Then
        MsgBox "You need to choose at least one way of calling the wizard!", _
            vbExclamation, "Choose interface"
        Exit Sub
    End If
    
    Unload Me [B][COLOR=red]'unload before attempting to delete control[/COLOR][/B]
    
    Call BuildUI
    shtControl.Range("B25") = True
    
End Sub

 

[Colin Legg]

Hi Jon,

The userform won't be unloaded at that point, because event handler code of a commandbutton embedded on the userform is still running. The userform will only be unloaded once the click event handler has finished.

 

[Jon von der Heyden]

Gotcha! Thanks so much Colin (again!).