Disabling/enabling events with a button created by code??

[Simon Lloyd]

Hello all!,

I have created a custom button that when clicked displays all hidde
sheets and also displays the worksheet menu bar, what i would like t
happen is that when this button is clicked that along with the above
new button appears on the menubar marked disable/enable, so that whe
this button is clicked it disables events for that sheet and whe
clicked again enables events!

Thanks,

Simon

 

[William]

Hi Simon

Incorporate the following into your existing sub to create the new button...

--------------------
Dim c As Variant
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
--------------------

and add this new sub....

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



--
XL2002
Regards

William

(e-mail address removed)

| Hello all!,
|
| I have created a custom button that when clicked displays all hidden
| sheets and also displays the worksheet menu bar, what i would like to
| happen is that when this button is clicked that along with the above a
| new button appears on the menubar marked disable/enable, so that when
| this button is clicked it disables events for that sheet and when
| clicked again enables events!
|
| Thanks,
|
| Simon.
|
|
| ---
| Message posted
|

 

[Simon Lloyd]

Thanks for that William!

Will this code work in xl97?...sorry forgot to mention that!

Simo

 

[William]

Hi Simon

I think so but unsure as tested in XL2002

--
XL2002
Regards

William

(e-mail address removed)

| Thanks for that William!
|
| Will this code work in xl97?...sorry forgot to mention that!
|
| Simon
|
|
| ---
| Message posted
|

 

[Simon Lloyd]

Hi William,

Well i tried your code and inserted it word for word only i kee
getting an error saying method of class is unsupported!, when i debu
it tells me that "c" is empty, any ideas what im doing wrong!

Simo

 

[William]

Hi Simon

Try this....

--------------------
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
--------------------

--
XL2002
Regards

William

(e-mail address removed)

| Hi William,
|
| Well i tried your code and inserted it word for word only i keep
| getting an error saying method of class is unsupported!, when i debug
| it tells me that "c" is empty, any ideas what im doing wrong!
|
| Simon
|
|
| ---
| Message posted
|