Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling/enabling events with a button created by code??
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling/enabling events with a button created by code??
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, Befo=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 "Simon Lloyd " wrote in message ... | 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 from http://www.ExcelForum.com/ | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling/enabling events with a button created by code??
Thanks for that William!
Will this code work in xl97?...sorry forgot to mention that! Simo -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling/enabling events with a button created by code??
Hi Simon
I think so but unsure as tested in XL2002 -- XL2002 Regards William "Simon Lloyd " wrote in message ... | Thanks for that William! | | Will this code work in xl97?...sorry forgot to mention that! | | Simon | | | --- | Message posted from http://www.ExcelForum.com/ | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling/enabling events with a button created by code??
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling/enabling events with a button created by code??
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, Befo=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 "Simon Lloyd " wrote in message ... | 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 from http://www.ExcelForum.com/ | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
enabling/disabling a set of code from within a spreadsheet sheet | Excel Discussion (Misc queries) | |||
Enabling Events | Excel Discussion (Misc queries) | |||
Disabling code using a button? | Excel Programming | |||
Disabling/Enabling Macros | Excel Programming | |||
Enabling/Disabling Macros | Excel Programming |