Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
Excel 2000.
This my first attempt to use a Command button (from the Control Toolbox) and neither Help nor Google have provided an obvious solution to my problem. In a recent reply to another question, Gord Dibben suggested using a worksheet event code to change the font colour in order to highlight data entered when editing a worksheet. Gord then suggested that one way of toggling this event code on or off could be to use a Command button. The codes work perfectly but I cannot locate my new Command button statically on the toolbar but only on the worksheet where it scrolls with the sheet. I cannot use a Custom button that will locate on the toolbar because they are associated with macros and my coding knowledge is negligible. Possible solutions might be: i) Extend Gord's main code to include a "hotkey" type toggle switch. ii) Some code to place a suitable button on a toolbar. Gord's code: CommandButton1 Private Sub CommandButton1_Click() Application.EnableEvents = False = Not _ Application.EnableEvents = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Value < "" Then .Font.ColorIndex = 3 End If End With ws_exit: Application.EnableEvents = True End Sub Any help or links would be greatly appreciated. -- Fred |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
Fred
If I understand what you want you can put a button on a toolbar and assign a macro to it. I have xl2000 & here is what I do: Goto toolscustomizeselect the Commands tab scroll down to "Macros" in the categories window & selectpick one of the buttons on commands side of the window. You need to drag the selected button to the desired toolbar and drop it. Right click on the button and from the menu select "assign macro". Asssign your macro!!! Remember to modify this button you must have the customize window open. To remove this button, with the customize window open, drag it off the toolbar and drop it in the middle of the screen anywhere. It will be gone. Mike Rogers -- The only dumb question is the one you did not ask. "Fred" wrote: Excel 2000. This my first attempt to use a Command button (from the Control Toolbox) and neither Help nor Google have provided an obvious solution to my problem. In a recent reply to another question, Gord Dibben suggested using a worksheet event code to change the font colour in order to highlight data entered when editing a worksheet. Gord then suggested that one way of toggling this event code on or off could be to use a Command button. The codes work perfectly but I cannot locate my new Command button statically on the toolbar but only on the worksheet where it scrolls with the sheet. I cannot use a Custom button that will locate on the toolbar because they are associated with macros and my coding knowledge is negligible. Possible solutions might be: i) Extend Gord's main code to include a "hotkey" type toggle switch. ii) Some code to place a suitable button on a toolbar. Gord's code: CommandButton1 Private Sub CommandButton1_Click() Application.EnableEvents = False = Not _ Application.EnableEvents = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Value < "" Then .Font.ColorIndex = 3 End If End With ws_exit: Application.EnableEvents = True End Sub Any help or links would be greatly appreciated. -- Fred |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
Fred,
Here's a quick way to keep your button visible: Make your first row tall enough to have the button in its area and move the button up into row 1. Then choose cell A2 and from the menu bar: Window | Freeze Now as you scroll down the sheet, it will remain visible. If you also need it to remain visible as you scroll down and right, then make cell A1 large enough to hold it and move the button into cell A1. Then go to cell B2 and use Window | Freeze - the button (and row 1 and column A) will always remain visible. Look into help for customizing toolbars to see how to set up a button on the toolbar that is tied to a macro. "Fred" wrote: Excel 2000. This my first attempt to use a Command button (from the Control Toolbox) and neither Help nor Google have provided an obvious solution to my problem. In a recent reply to another question, Gord Dibben suggested using a worksheet event code to change the font colour in order to highlight data entered when editing a worksheet. Gord then suggested that one way of toggling this event code on or off could be to use a Command button. The codes work perfectly but I cannot locate my new Command button statically on the toolbar but only on the worksheet where it scrolls with the sheet. I cannot use a Custom button that will locate on the toolbar because they are associated with macros and my coding knowledge is negligible. Possible solutions might be: i) Extend Gord's main code to include a "hotkey" type toggle switch. ii) Some code to place a suitable button on a toolbar. Gord's code: CommandButton1 Private Sub CommandButton1_Click() Application.EnableEvents = False = Not _ Application.EnableEvents = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Value < "" Then .Font.ColorIndex = 3 End If End With ws_exit: Application.EnableEvents = True End Sub Any help or links would be greatly appreciated. -- Fred |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
Fred
Be very careful with that toggle on/off code I posted. Could leave you with events disabled when you don't want them disabled if you forget to turn back on. I was hoping someone would help us with a method to just disable for that worksheet. Gord On Sat, 28 Jul 2007 22:44:33 +0100, "Fred" wrote: Excel 2000. This my first attempt to use a Command button (from the Control Toolbox) and neither Help nor Google have provided an obvious solution to my problem. In a recent reply to another question, Gord Dibben suggested using a worksheet event code to change the font colour in order to highlight data entered when editing a worksheet. Gord then suggested that one way of toggling this event code on or off could be to use a Command button. The codes work perfectly but I cannot locate my new Command button statically on the toolbar but only on the worksheet where it scrolls with the sheet. I cannot use a Custom button that will locate on the toolbar because they are associated with macros and my coding knowledge is negligible. Possible solutions might be: i) Extend Gord's main code to include a "hotkey" type toggle switch. ii) Some code to place a suitable button on a toolbar. Gord's code: CommandButton1 Private Sub CommandButton1_Click() Application.EnableEvents = False = Not _ Application.EnableEvents = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Value < "" Then .Font.ColorIndex = 3 End If End With ws_exit: Application.EnableEvents = True End Sub Any help or links would be greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
I wondered about that and almost mentioned same warning. But oddly (or maybe
I got in a hurry and did a boo-boo) when I used a command button from the Control Toolbox (not the Forms tools) it actually appeared to respond to multiple clicks. I didn't delve into it any further than that to see the status of any other event handlers during those times. Perhaps later this evening - running down a Hotfix for 2007 now. "Gord Dibben" wrote: Fred Be very careful with that toggle on/off code I posted. Could leave you with events disabled when you don't want them disabled if you forget to turn back on. I was hoping someone would help us with a method to just disable for that worksheet. Gord On Sat, 28 Jul 2007 22:44:33 +0100, "Fred" wrote: Excel 2000. This my first attempt to use a Command button (from the Control Toolbox) and neither Help nor Google have provided an obvious solution to my problem. In a recent reply to another question, Gord Dibben suggested using a worksheet event code to change the font colour in order to highlight data entered when editing a worksheet. Gord then suggested that one way of toggling this event code on or off could be to use a Command button. The codes work perfectly but I cannot locate my new Command button statically on the toolbar but only on the worksheet where it scrolls with the sheet. I cannot use a Custom button that will locate on the toolbar because they are associated with macros and my coding knowledge is negligible. Possible solutions might be: i) Extend Gord's main code to include a "hotkey" type toggle switch. ii) Some code to place a suitable button on a toolbar. Gord's code: CommandButton1 Private Sub CommandButton1_Click() Application.EnableEvents = False = Not _ Application.EnableEvents = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Value < "" Then .Font.ColorIndex = 3 End If End With ws_exit: Application.EnableEvents = True End Sub Any help or links would be greatly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
"Gord Dibben" <gorddibbATshawDOTca wrote in message
... Fred Be very careful with that toggle on/off code I posted. Could leave you with events disabled when you don't want them disabled if you forget to turn back on. I was hoping someone would help us with a method to just disable for that worksheet. Thanks for everyone's replies. Particular thanks to Gord for posting the original code - despite my lack of knowledge on this subject, all warnings are duly heeded :-) I am using the technique to learn a little more about coding, and the ability to highlight edited cells in a large worksheet appealed as a worthwhile project that would certainly be better than my present method of tracking multiple changes! Mike, Thanks but my problem is that the toggle code is not, as I understand things, a "macro" and my newfound attempts to uses that method did not work - I don't know the correct terminology. JL, Yes I am currently using your method but I was hoping to be able to use a button because my large worksheet already uses the top three rows for titles. And yes, I have discovered that the command button does respond to multiple clicks. Gord, I deliberately posted this as a new question in the hope that somebody would suggest "another way" rather than the usual "one way"! Thanks again. -- Fred |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
In , Fred
spake thusly: This my first attempt to use a Command button [. . . .] The codes work perfectly but I cannot locate my new Command button statically on the toolbar but only on the worksheet where it scrolls with the sheet. To keep the button visible even when the sheet scrolls, go to Design Mode in the Control toolbox; right-click on your button and choose "Format Control"; and, under the Properties tab, select the "Object positioning" radio button that works best for your needs. The third of the three choices I see in Excel 2002 is: "Don't move or size with cells." You said you use Excel 2000, but I believe this will work there as well. -- dman |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
Well, I'm not really all that great when dealing with modifying built in
toolbars and menus, but this appears to work in 2003 for me and I haven't blown it up quite yet. But the following would add a custom (smiley face) button to the Standard toolbar (the one with icons for New Workbook, Open Workbook, Save, eMail, Print, etc) when you open the workbook and then delete it when you close the workbook. Code has to go into two places: first part into a regular code module, second part into the Workbook events code area. If you already have things set up to use Option Explicit, when you paste the code into the modules make sure that the phrase doesn't appear twice at the top of the code modules. Put this code into a regular code module - might as well have its own module, so: [Alt]+[F11] to open the VB Editor, Insert | Module from that menu and copy and paste this into it (remember to check for double "Option Explicit") Option Explicit Public Const whatToolbar = "Standard" Public Const macroName = "ToggleEventProcessing" Sub ToggleEventProcessing() Dim controlCount As Integer controlCount = Application.CommandBars(whatToolbar).Controls.Coun t Application.EnableEvents = False = Not _ Application.EnableEvents = False If Application.EnableEvents = True Then Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = "Disable Events" Else Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = "Enable Events" End If End Sub Now for the second piece which needs to go into ThisWorkbook code area. Assuming the VB Editor is closed, right-click the Excel icon immediately to the left of File in the menu bar and choose [View Code] then copy and paste this code into the code module presented to you (again remembering to check for double entry of "Option Explicit") Option Explicit Private Sub Workbook_Open() Dim controlCount As Integer controlCount = Application.CommandBars(whatToolbar).Controls.Coun t + 1 Application.CommandBars(whatToolbar).Controls.Add Type:=msoControlButton, _ ID:=2950, Befo=controlCount Application.CommandBars(whatToolbar).Controls(cont rolCount).OnAction = _ macroName If Application.EnableEvents = True Then Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = "Disable Events" Else Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = "Enable Events" End If End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim controlCount As Integer Dim onActionValue As String 'find and delete the added button 'so it doesn't show up in other workbooks 'and try to use this workbook as the macro source controlCount = Application.CommandBars(whatToolbar).Controls.Coun t onActionValue = _ Application.CommandBars(whatToolbar).Controls(cont rolCount).OnAction 'make sure we don't wipe out a standard button! If InStr(onActionValue, macroName) Then Application.CommandBars(whatToolbar).Controls(cont rolCount).Delete End If 'if .EnableEvents is false, set back to true If Application.EnableEvents = False Then Application.EnableEvents = True End If End Sub Hope this works out for you. "Fred" wrote: "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Fred Be very careful with that toggle on/off code I posted. Could leave you with events disabled when you don't want them disabled if you forget to turn back on. I was hoping someone would help us with a method to just disable for that worksheet. Thanks for everyone's replies. Particular thanks to Gord for posting the original code - despite my lack of knowledge on this subject, all warnings are duly heeded :-) I am using the technique to learn a little more about coding, and the ability to highlight edited cells in a large worksheet appealed as a worthwhile project that would certainly be better than my present method of tracking multiple changes! Mike, Thanks but my problem is that the toggle code is not, as I understand things, a "macro" and my newfound attempts to uses that method did not work - I don't know the correct terminology. JL, Yes I am currently using your method but I was hoping to be able to use a button because my large worksheet already uses the top three rows for titles. And yes, I have discovered that the command button does respond to multiple clicks. Gord, I deliberately posted this as a new question in the hope that somebody would suggest "another way" rather than the usual "one way"! Thanks again. -- Fred |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
... this appears to work in 2003 <snip Hope this works out for you. JL, Firstly, thanks for taking the time to create this new code. Sorry for the delay in responding. Part of this delay was due to testing before trying to explain what I thought was an elusive problem but which I now realise is a feature of Gord's original code that I hadn't noticed before. I found that once a cell has been edited in red, then that cell's format font is changed to red and further toggling of the new button has no effect in that cell. Now that I understand what actually happens it is not a problem, however I would be interested to know if there is a simple way to modify the code to give a true toggle on/off action. Also, before I could get your new code to run, I had to rem out the lines that create the new button captions because they generated syntax errors - could this be an Excel 2000 problem? As one final refinement, could the red also be bold? Thanks again for your help. -- Fred |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
"Dallman Ross" <dman@localhost. wrote in message
... In , Fred spake thusly: To keep the button visible even when the sheet scrolls, go to Design Mode in the Control toolbox; right-click on your button and choose "Format Control"; and, under the Properties tab, select the "Object positioning" radio button that works best for your needs. The third of the three choices I see in Excel 2002 is: "Don't move or size with cells." You said you use Excel 2000, but I believe this will work there as well. -- dman dman, Thanks for your suggestion. That feature is in Excel 2000 but when I tried all of the options each one did as expected, but only in the position on the worksheet where the button was located. Unfortunately the button still scrolls with the sheet. It would be interesting to know if the feature does lock the scrolling in Excel 2002. Thanks again. -- Fred |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
In , Fred
spake thusly: "Dallman Ross" <dman@localhost. wrote in message ... To keep the button visible even when the sheet scrolls, go to Design Mode in the Control toolbox; right-click on your button and choose "Format Control"; and, under the Properties tab, That feature is in Excel 2000 but when I tried all of the options each one did as expected, but only in the position on the worksheet where the button was located. Unfortunately the button still scrolls with the sheet. It would be interesting to know if the feature does lock the scrolling in Excel 2002. Okay, you are right in your suspicion, in that in 2002 as well the button only stays fixed in relation to the cells where it was placed, but can still be scrolled off the screen. Perhaps the OP's recommendation to put the button at the top would be best, then. I noticed another poster -- I think it was JLatham, but can't look right this second -- tried out his hand at sime VBA code to make the button part of the toolbar, too. I actually saved that to try it later, if and when I have a need. -- dman |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
Fred,
It's very possible that you had to remark out those particular lines because of version differences. I used 2003 to do the development/testing. It's probably some other property in 2000 like "ToolTip" or something - I just don't know, to be honest. As far as the toggling of red/bold, etc. I haven't looked at that code. All the stuff I provided simply creates/deletes the button in the toolbar and that button simply toggles .EnableEvents on/off. Can you post the code you have now that you have questions regarding colors? Did you slide that into the button code in place of the .EnableEvents code? I can understand why, but I also need to know where the code is at - so if you can just paste it up, it would really help. Does the extra button come and go properly? Like I said, this isn't something I do very often at all - this is maybe the 2nd time in a couple of years. I also have some existing code around here to do much the same thing with a Menu list/pull-down list if you decide to go with that some time instead of with the button in the toolbar. "Fred" wrote: "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... this appears to work in 2003 <snip Hope this works out for you. JL, Firstly, thanks for taking the time to create this new code. Sorry for the delay in responding. Part of this delay was due to testing before trying to explain what I thought was an elusive problem but which I now realise is a feature of Gord's original code that I hadn't noticed before. I found that once a cell has been edited in red, then that cell's format font is changed to red and further toggling of the new button has no effect in that cell. Now that I understand what actually happens it is not a problem, however I would be interested to know if there is a simple way to modify the code to give a true toggle on/off action. Also, before I could get your new code to run, I had to rem out the lines that create the new button captions because they generated syntax errors - could this be an Excel 2000 problem? As one final refinement, could the red also be bold? Thanks again for your help. -- Fred |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
... Fred, Colours. Incidentally, the file always opens on red - ideally it would be better if it started on black then the button would only need to be pushed when editing. Did you slide that into the button code in place of the .EnableEvents code? No because I don't know enough about what I am trying to do! Your button points to your code i.e. 'filename.xls'!ToggleEventProcessing (see below) Does the extra button come and go properly? No - it only goes after exiting on red. < Menu list/pull-down list Yes I would be very interested to see the menu version if you care to post it. My three pieces of code a ================================================== ======= ================================================== ======= filename.xls - Module1 (Code) (General) (ToggleEventProcessing)) Option Explicit Public Const whatToolbar = "Standard" Public Const macroName = "ToggleEventProcessing" ----------------------------------------------------------- Sub ToggleEventProcessing() Dim controlCount As Integer controlCount = 0 controlCount = Application.CommandBars(whatToolbar).Controls.Coun t Application.EnableEvents = False = Not _ Application.EnableEvents = False ' If Application.EnableEvents = True Then ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Disable Events" ' Else ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Enable Events" ' End If End Sub ================================================== ======= ================================================== ======= filename.xls - ThisWorkbook (Code) (General) (Declarations) Option Explicit ----------------------------------------------------------- Private Sub Workbook_Open() Dim controlCount As Integer controlCount = Application.CommandBars(whatToolbar).Controls.Coun t + 1 Application.CommandBars(whatToolbar).Controls.Add Type:=msoControlButton, _ ID:=2950, Befo=controlCount Application.CommandBars(whatToolbar).Controls(cont rolCount).OnAction = _ macroName ' If Application.EnableEvents = True Then ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Disable Events" ' Else ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Enable Events" ' End If End Sub ----------------------------------------------------------- Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim controlCount As Integer Dim onActionValue As String 'find and delete the added button 'so it doesn't show up in other workbooks 'and try to use this workbook as the macro source controlCount = Application.CommandBars(whatToolbar).Controls.Coun t onActionValue = _ Application.CommandBars(whatToolbar).Controls(cont rolCount).OnAction 'make sure we don't wipe out a standard button! If InStr(onActionValue, macroName) Then Application.CommandBars(whatToolbar).Controls(cont rolCount).Delete End If 'if .EnableEvents is false, set back to true If Application.EnableEvents = False Then Application.EnableEvents = True End If End Sub ================================================== ======= ================================================== ======= Filename.xls - Sheet1 (Code) (Worksheet) (Change) ' Gord's code Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Value < "" Then .Font.ColorIndex = 3 End If End With ws_exit: Application.EnableEvents = True End Sub ================================================== ======= ================================================== ======= Thanks for your help. -- Fred |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
Well, Gord's code and mine are pretty much totally independent. The only
thing they have in common is the fact that they alter the .EnableEvent property. You could change his IF statement to this: If .Value < "" Then .Font.ColorIndex 3 ' Red Else .Font.ColorIndex = xlAutomatic End If But that's not going to cure the whole problem, because his code pretty much says that when something changes to anything, make it red. So starting from an empty cell and adding brand new, unedited text sets it to red. It's a little difficult to tell the contents of a cell at this point. But if we back up one step and use the _SelectionChange() event also, then maybe we can deal with it. In the worksheet code, declare a variable up above any Sub or Function sections as Dim newSelectionValue As Variant by declaring it there, it becomes 'visible' to all routines in that same module. Add this code to the module, note that it changes Gord's code somewhat and I've shown the new variable declaration also: Dim newSelectionValue As Variant Private Sub Worksheet_SelectionChange(ByVal Target As Range) newSelectionValue = Target.Value End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If newSelectionValue < "" Then .Font.ColorIndex = 3 Else .Font.ColorIndex = xlAutomatic End If End With newSelectionValue = Target.Value ' for next change ws_exit: Application.EnableEvents = True End Sub Here's what happens: you move into a cell (SelectionChange) and newSelectionValue picks up whatever value that cell has. So now we 'remember' what was in it before it gets changed, if it does. Then when you change it's value, the test says "was it empty before?" and if it was, then the initial text color is set to xlAutomatic (black usually). But if there was text or values in it, then it gets changed to red and it's going to stay red until doomsday until you [Del] the contents of it. Then it's like starting all over, next entry will become black. And we pick up the new value of Target for use in case you don't change cells somehow and do something else to the same one. I'll have to look at the issue of the button remaining in the toolbar at times - that may happen because when you exit, .EnableEvents is False. If that's the case, there's no way around it other than for you to make sure that .EnableEvents is true 'manually' before closing the workbook. Give me a few minutes (or more) and I'll upload an Excel 97-2003 compatible workbook to my website that will show a way to add a new menu to the workbook, you can download it and examine the code and such for yourself. I'll post back with a link later. "Fred" wrote: "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Fred, Colours. Incidentally, the file always opens on red - ideally it would be better if it started on black then the button would only need to be pushed when editing. Did you slide that into the button code in place of the .EnableEvents code? No because I don't know enough about what I am trying to do! Your button points to your code i.e. 'filename.xls'!ToggleEventProcessing (see below) Does the extra button come and go properly? No - it only goes after exiting on red. < Menu list/pull-down list Yes I would be very interested to see the menu version if you care to post it. My three pieces of code a ================================================== ======= ================================================== ======= filename.xls - Module1 (Code) (General) (ToggleEventProcessing)) Option Explicit Public Const whatToolbar = "Standard" Public Const macroName = "ToggleEventProcessing" ----------------------------------------------------------- Sub ToggleEventProcessing() Dim controlCount As Integer controlCount = 0 controlCount = Application.CommandBars(whatToolbar).Controls.Coun t Application.EnableEvents = False = Not _ Application.EnableEvents = False ' If Application.EnableEvents = True Then ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Disable Events" ' Else ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Enable Events" ' End If End Sub ================================================== ======= ================================================== ======= filename.xls - ThisWorkbook (Code) (General) (Declarations) Option Explicit ----------------------------------------------------------- Private Sub Workbook_Open() Dim controlCount As Integer controlCount = Application.CommandBars(whatToolbar).Controls.Coun t + 1 Application.CommandBars(whatToolbar).Controls.Add Type:=msoControlButton, _ ID:=2950, Befo=controlCount Application.CommandBars(whatToolbar).Controls(cont rolCount).OnAction = _ macroName ' If Application.EnableEvents = True Then ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Disable Events" ' Else ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Enable Events" ' End If End Sub ----------------------------------------------------------- Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim controlCount As Integer Dim onActionValue As String 'find and delete the added button 'so it doesn't show up in other workbooks 'and try to use this workbook as the macro source controlCount = Application.CommandBars(whatToolbar).Controls.Coun t onActionValue = _ Application.CommandBars(whatToolbar).Controls(cont rolCount).OnAction 'make sure we don't wipe out a standard button! If InStr(onActionValue, macroName) Then Application.CommandBars(whatToolbar).Controls(cont rolCount).Delete End If 'if .EnableEvents is false, set back to true If Application.EnableEvents = False Then Application.EnableEvents = True End If End Sub ================================================== ======= ================================================== ======= Filename.xls - Sheet1 (Code) (Worksheet) (Change) ' Gord's code Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Value < "" Then .Font.ColorIndex = 3 End If End With ws_exit: Application.EnableEvents = True End Sub ================================================== ======= ================================================== ======= Thanks for your help. -- Fred |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
Here's link to the file that has code to create a custom menu - I tested it
in both 2003 and 2000. There was one piece that didn't work in 2000, so I set things up to not do that when in 2000. Just click the link and save to hard drive: http://www.jlathamsite.com/uploads/C...mExcelMenu.xls "Fred" wrote: "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Fred, Colours. Incidentally, the file always opens on red - ideally it would be better if it started on black then the button would only need to be pushed when editing. Did you slide that into the button code in place of the .EnableEvents code? No because I don't know enough about what I am trying to do! Your button points to your code i.e. 'filename.xls'!ToggleEventProcessing (see below) Does the extra button come and go properly? No - it only goes after exiting on red. < Menu list/pull-down list Yes I would be very interested to see the menu version if you care to post it. My three pieces of code a ================================================== ======= ================================================== ======= filename.xls - Module1 (Code) (General) (ToggleEventProcessing)) Option Explicit Public Const whatToolbar = "Standard" Public Const macroName = "ToggleEventProcessing" ----------------------------------------------------------- Sub ToggleEventProcessing() Dim controlCount As Integer controlCount = 0 controlCount = Application.CommandBars(whatToolbar).Controls.Coun t Application.EnableEvents = False = Not _ Application.EnableEvents = False ' If Application.EnableEvents = True Then ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Disable Events" ' Else ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Enable Events" ' End If End Sub ================================================== ======= ================================================== ======= filename.xls - ThisWorkbook (Code) (General) (Declarations) Option Explicit ----------------------------------------------------------- Private Sub Workbook_Open() Dim controlCount As Integer controlCount = Application.CommandBars(whatToolbar).Controls.Coun t + 1 Application.CommandBars(whatToolbar).Controls.Add Type:=msoControlButton, _ ID:=2950, Befo=controlCount Application.CommandBars(whatToolbar).Controls(cont rolCount).OnAction = _ macroName ' If Application.EnableEvents = True Then ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Disable Events" ' Else ' Application.CommandBars(whatToolbar).Controls(cont rolCount).Caption = ' "Enable Events" ' End If End Sub ----------------------------------------------------------- Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim controlCount As Integer Dim onActionValue As String 'find and delete the added button 'so it doesn't show up in other workbooks 'and try to use this workbook as the macro source controlCount = Application.CommandBars(whatToolbar).Controls.Coun t onActionValue = _ Application.CommandBars(whatToolbar).Controls(cont rolCount).OnAction 'make sure we don't wipe out a standard button! If InStr(onActionValue, macroName) Then Application.CommandBars(whatToolbar).Controls(cont rolCount).Delete End If 'if .EnableEvents is false, set back to true If Application.EnableEvents = False Then Application.EnableEvents = True End If End Sub ================================================== ======= ================================================== ======= Filename.xls - Sheet1 (Code) (Worksheet) (Change) ' Gord's code Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Value < "" Then .Font.ColorIndex = 3 End If End With ws_exit: Application.EnableEvents = True End Sub ================================================== ======= ================================================== ======= Thanks for your help. -- Fred |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to toggle worksheet event code on / off?
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
... Well, Gord's code and mine are pretty much totally independent. JL, Thanks for your reply and also for your new menu code. Frustratingly, I am forced to take a short break from this project but I will be back in contact as soon as I get the chance. I really appreciate the help that you have given me. -- Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code behind command button | Excel Worksheet Functions | |||
Starting/Stopping Worksheet Event Code | Excel Discussion (Misc queries) | |||
Where?Worksheet code module or Worksheet_SelectionChange event han | Excel Worksheet Functions | |||
Command Button VBA code | Excel Discussion (Misc queries) | |||
Worksheet Event Code | Excel Worksheet Functions |