"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