Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,907
- Office Version
- 365
- Platform
- Windows
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
ClearUI
frmSetup:
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
Code:
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
Code:
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:
Code:
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