Making Worksheet Menu Bar button sink or raise???

[Simon Lloyd]

Hello all!,

I have this code which creates a button on Auto_open to allow the use
to enable/disable events and it works perfect, although i would lik
the button to show sunken when clicked first time and raised or norma
when clicked again! or change colour even which ever is easiest.

Can anyone help??

Simon.

Here's the code.....

Sub auto_open()
Dim c As Variant
On Error Resume Next
With Application.CommandBars("worksheet menu bar")
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
End With
End Sub

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

 

[MSP77079]

Why don't you change the caption?

Or, instead of style msoButtonCaption, use msoButtonIconAndCaption an
change the icon ...

from say, the up arrow (.FaceId = 134) to the down arrow (.FaceId
135).

Or from the lightening bolt (.FaceId = 346) to the lightening bolt wit
an X through it (.FaceId = 348).

The documentation on the available Face Ids is somewhat slim. So I'v
written a macro that will create a toolbar full of icons. Here it is:

Sub ButtonIDs()

' The purpose of this macro is to help us find FaceId numbers
' for custom toolbar buttons
'
' This macro creates a tool bar with up to 400 buttons (plus 1)
' each button on the tool bar (except the plus 1) calls
' a macro named "TellMeWho" (in Module2), which tells us
' the FaceId of the button we pressed.
'
' The "plus 1" button calls this macro again. Therefore, users
' can call this macro recursively to find every FaceId between
' 1 and 3518. Note that calling a FaceId greater than 3518 creates
' an "out of range" error.


'if a toolbar named "MyCustomBar" exists, then count number of button
on it
thisID = 0
numBars = CommandBars.Count
For Each Bar In CommandBars
If Bar.Name = "MyCustomBar" Then
numControls = Bar.Controls.Count
thisID = Bar.Controls(numControls - 2).FaceId
End If
Next Bar
AskForChoice:
myChoice = MsgBox("Do you want to show buttons?", vbOKCancel, _
"This Macro will create a toolbar with up to 40
buttons.")
If myChoice = vbCancel Then GoTo EndMacro

EnterNumberHere:
' myNum = Application.InputBox("Enter a number")
Prompt = "Enter a number between 1 and 3118"
Title = "Where do you want to begin?"
If thisID > 1 Then _
Title = "The last button displayed was " & thisID
DefaultValue = thisID + 1
myNum = Application.InputBox(Prompt, Title, DefaultValue)
If myNum = "False" Then GoTo AskForChoice
myNum = myNum + 0 'convert from text to value
' If myNum < 1 Or myNum > 3100 Then
' MsgBox "please enter a number between 1 and 3118"
' GoTo EnterNumberHere
' End If

'if a toolbar named "MyCustomBar" exists, then delete it
For Each Bar In CommandBars
If Bar.Name = "MyCustomBar" Then Bar.Delete
Next Bar
'create a blank custom toolbar
CommandBars.Add(Name:="MyCustomBar").Visible = False
Set myBar = CommandBars("MyCustomBar")
'add buttons to the toolbar
For i = myNum To myNum + 399
On Error GoTo ShowTheToolbar
Set myControl = myBar.Controls.Add(Type:=msoControlButton)
myControl.FaceId = i
myControl.Caption = i
myControl.OnAction = "TellMeWho" 'in Module2
Next i

ShowTheToolbar:
Set myControl = myBar.Controls.Add(Type:=msoControlButton)
myControl.FaceId = 453
myControl.Caption = "Show More Buttons"
myControl.OnAction = "ButtonIDs"
myControl.Style = msoButtonIconAndCaption

Set myControl = myBar.Controls.Add(Type:=msoControlButton)
myControl.FaceId = 3764
myControl.Caption = "Kill Toolbar"
myControl.OnAction = "KillToolbar"
myControl.Style = msoButtonIconAndCaption

With myBar
.Visible = True
.Width = 500
.Left = 200
.Top = 150
End With

myChoice = MsgBox("Mouse-over or click on button to see it
FaceId", _
vbOKOnly, "This toolbar shows buttons " & myNum & " to "
i)

EndMacro:
End Sub

Sub TellMeWho()

' This macro creates a message box telling us the
' FaceId of the toolbar icon that called this macro

Set theCtrl = CommandBars.ActionControl
thisID = theCtrl.FaceId
MsgBox "You pressed button number " & thisID

End Sub

Sub KillToolbar()
'if a toolbar named "MyCustomBar" exists, then delete it
For Each Bar In CommandBars
If Bar.Name = "MyCustomBar" Then Bar.Delete
Next Bar
End Su

 

[Dave Peterson]

Option Explicit
Sub enevents()
Dim IsUp As Boolean
IsUp = (Not Application.EnableEvents)

With Application
.EnableEvents = IsUp
With .CommandBars.ActionControl
.State = IsUp
End With
End With
End Sub

 

[Simon Lloyd]

Thanks for the help!

MSP a fantastic amount of information i pasted the code to try it ou
but it didnt work i added On Error Resume Next which got a little extr
way through but it still stopped i will look at it over the week (i'
using xl97).

Dave, just what i needed thanks!

Simo