Command button to toggle worksheet event code on / off?

[Fred]

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.

 

[Mike Rogers]

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 tools>customize>select
the Commands tab> scroll down to "Macros" in the categories window &
select>pick 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

 

[JLatham]

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.

 

[Gord Dibben]

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

 

[JLatham]

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.

 

[Fred]

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.

 

[Dallman Ross]

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.

 

[JLatham]

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

Application.EnableEvents = False = Not _
Application.EnableEvents = False

If Application.EnableEvents = True Then
Application.CommandBars(whatToolbar).Controls(controlCount).Caption =
"Disable Events"
Else
Application.CommandBars(whatToolbar).Controls(controlCount).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.Count + 1

Application.CommandBars(whatToolbar).Controls.Add Type:=msoControlButton, _
ID:=2950, Before:=controlCount
Application.CommandBars(whatToolbar).Controls(controlCount).OnAction = _
macroName
If Application.EnableEvents = True Then
Application.CommandBars(whatToolbar).Controls(controlCount).Caption =
"Disable Events"
Else
Application.CommandBars(whatToolbar).Controls(controlCount).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.Count
onActionValue = _
Application.CommandBars(whatToolbar).Controls(controlCount).OnAction
'make sure we don't wipe out a standard button!
If InStr(onActionValue, macroName) Then
Application.CommandBars(whatToolbar).Controls(controlCount).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]

this appears to work in 2003

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]

In <[email protected]>, Fred
<[email protected]> 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,

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.

 

[Dallman Ross]

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.

 

[JLatham]

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]

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

=========================================================
=========================================================

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

Application.EnableEvents = False = Not _
Application.EnableEvents = False

' If Application.EnableEvents = True Then
' Application.CommandBars(whatToolbar).Controls(controlCount).Caption =
' "Disable Events"
' Else
' Application.CommandBars(whatToolbar).Controls(controlCount).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.Count + 1

Application.CommandBars(whatToolbar).Controls.Add Type:=msoControlButton,
_
ID:=2950, Before:=controlCount
Application.CommandBars(whatToolbar).Controls(controlCount).OnAction = _
macroName
' If Application.EnableEvents = True Then
' Application.CommandBars(whatToolbar).Controls(controlCount).Caption =
' "Disable Events"
' Else
' Application.CommandBars(whatToolbar).Controls(controlCount).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.Count
onActionValue = _
Application.CommandBars(whatToolbar).Controls(controlCount).OnAction
'make sure we don't wipe out a standard button!
If InStr(onActionValue, macroName) Then
Application.CommandBars(whatToolbar).Controls(controlCount).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.

 

[JLatham]

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.

 

[JLatham]

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/CreateCustomExcelMenu.xls

 

[Fred]

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.