Why does this code not create a button when called???

[Simon Lloyd]

I have this code for creating a toggle button to turn events on and off,
i call on this sub (Call en) in a procedure that says if the admin
password is ???? then with the application show command bars end with
and after that i use Call en it does not fault but it does not create a
button on the command bar, any ideas??

Simon.

Here's all the code im using

Sub en()
Dim c As Variant
On Error Resume Next
With Application
CommandBars.ActiveMenuBar.Enabled = True
For Each c In .Controls
If c.Caption = "EN" Then c.Delete
Next c
Controls.Add Type:=msoControlButton, Id:=2950, before:=1
Controls(1).Caption = "EN"
Controls(1).TooltipText = "Enable Events"
Controls(1).OnAction = ThisWorkbook.Name & "!enevents"
Controls(1).Style = msoButtonCaption
Worksheets("hidden").Visible = True
End With
End Sub

Private Sub TextBox1_Change()

If TextBox1.Text = "????" Then

With Application

CommandBars.ActiveMenuBar.Enabled = True

End With
Call en
ElseIf TextBox1.Value <> "????" Then

Exit Sub

End If



Unload Me

'Me.Hide
End Sub

Sub enevents()
Application.EnableEvents = Not Application.EnableEvents
End Sub

 

[Tom Ogilvy]

Your use of On Error Resume Next as the top hides all the errors in your
code. You may have more problems than this, but these untested changes may
help. I assumed the commandbar you speak of is the worksheet menu bar. If
not, make appropriate adjustments.

Sub en()
Dim c As Variant
'On Error Resume Next
With Application
.CommandBars.ActiveMenuBar.Enabled = True
For Each c In .Commandbars("Worksheet Menu Bar").Controls
If c.Caption = "EN" Then c.Delete
Next c
set cb = .CommandBars("Worksheet Menu Bar").Controls.Add( _
Type:=msoControlButton, Id:=2950, before:=1)
cb.Caption = "EN"
cb.TooltipText = "Enable Events"
cb.OnAction = ThisWorkbook.Name & "!enevents"
cb.Style = msoButtonCaption
Worksheets("hidden").Visible = True
End With
End Sub

 

[Simon Lloyd]

Thanks again Tom it worked a treat......one other querie?, how do i ge
the button to show suken or raised or perhaps highlighted or shadowe
when it is clicked so the user can see if it has been toggled?

Simon

 

[Tom Ogilvy]

Perhaps you mean this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213735&Product=xlw
XL2000: How to Place a Check Mark Next to a Custom Menu Item