Hi everyone,

The following code is used to change the colour in a cell after a
given (validated) entry. In the next cell the date of the change is
written and another cell is used for a dash or the word Yes when a
task is completed (5 purple).

This code works fine in several sheets and has been working until
recently in a sheet where I keep track of completed tasks.
What happens when I walk through the code step by step, is that the
code suddenly stops after ".Columns(2).Value = (Now)". I don't
understand what happens here. Any suggestions?

TIA, Rob
*******************
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim myRng As Range, Number As Integer
Application.EnableEvents = True
Number = Sh.Index
Select Case Number
Case 1, 2
With Target
If .Cells.Count > 1 Then Exit Sub
'If (.Row >= 4 And .Row <= 100 And .Column >= 8 And _
.Column Mod 2 = 0 And .Column <= 15) Then
If (.Row >= 4 And .Row <= 250 And .Column = 7) Then
Set myRng = Target.Offset(0, 0).Resize(1, 1)
Select Case LCase(Target.Value)
Case Is = "0 blue"
myRng.Interior.ColorIndex = 5
myRng.Font.ColorIndex = 2
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "1 orange"
myRng.Interior.ColorIndex = 46
myRng.Font.ColorIndex = 2
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "2 green"
myRng.Interior.ColorIndex = 4
myRng.Font.ColorIndex = 0
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "3 yellow"
myRng.Interior.ColorIndex = 6
myRng.Font.ColorIndex = 0
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "4 red"
myRng.Interior.ColorIndex = 3
myRng.Font.ColorIndex = 2
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "5 purple"
myRng.Interior.ColorIndex = 39
myRng.Font.ColorIndex = 2
.Columns(2).Value = (Now)
.Columns(3).Value = "Yes"
Case Else
'Set myRng = Target.Offset(0, -1).Resize(1, 1)
'myRng.Interior.ColorIndex = xlNone
Set myRng = Target.Offset(0, 0).Resize(1, 1)
myRng.Interior.ColorIndex = xlNone
.Columns(2).Value = ""
.Columns(3).Value = "-"
End Select
End If
End With
Case Else
End Select
End Sub
*******************

Hi Dqsp,

Near the head of your procedure, you
should change:

> Application.EnableEvents = True

to

On Error GoTo XIT
Application.EnableEvents = False

Then replace:

> End Sub


with

XIT:
Application.EnableEvents = True

End Sub

Otherwise, every cell value change effected
by the procedure will be considered as a
new change event and the procedure will r
ecursively call itself.



---
Regards.
Norman


"dqsp" <(E-Mail Removed)> wrote in message
news:48cb1c79-d2f5-4446-8bb7-(E-Mail Removed)...
> Hi everyone,
>
> The following code is used to change the colour in a cell after a
> given (validated) entry. In the next cell the date of the change is
> written and another cell is used for a dash or the word Yes when a
> task is completed (5 purple).
>
> This code works fine in several sheets and has been working until
> recently in a sheet where I keep track of completed tasks.
> What happens when I walk through the code step by step, is that the
> code suddenly stops after ".Columns(2).Value = (Now)". I don't
> understand what happens here. Any suggestions?
>
> TIA, Rob
> *******************
> Code:
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
> Range)
> Dim myRng As Range, Number As Integer
> Application.EnableEvents = True
> Number = Sh.Index
> Select Case Number
> Case 1, 2
> With Target
> If .Cells.Count > 1 Then Exit Sub
> 'If (.Row >= 4 And .Row <= 100 And .Column >= 8 And _
> .Column Mod 2 = 0 And .Column <= 15) Then
> If (.Row >= 4 And .Row <= 250 And .Column = 7) Then
> Set myRng = Target.Offset(0, 0).Resize(1, 1)
> Select Case LCase(Target.Value)
> Case Is = "0 blue"
> myRng.Interior.ColorIndex = 5
> myRng.Font.ColorIndex = 2
> .Columns(2).Value = (Now)
> .Columns(3).Value = "-"
> Case Is = "1 orange"
> myRng.Interior.ColorIndex = 46
> myRng.Font.ColorIndex = 2
> .Columns(2).Value = (Now)
> .Columns(3).Value = "-"
> Case Is = "2 green"
> myRng.Interior.ColorIndex = 4
> myRng.Font.ColorIndex = 0
> .Columns(2).Value = (Now)
> .Columns(3).Value = "-"
> Case Is = "3 yellow"
> myRng.Interior.ColorIndex = 6
> myRng.Font.ColorIndex = 0
> .Columns(2).Value = (Now)
> .Columns(3).Value = "-"
> Case Is = "4 red"
> myRng.Interior.ColorIndex = 3
> myRng.Font.ColorIndex = 2
> .Columns(2).Value = (Now)
> .Columns(3).Value = "-"
> Case Is = "5 purple"
> myRng.Interior.ColorIndex = 39
> myRng.Font.ColorIndex = 2
> .Columns(2).Value = (Now)
> .Columns(3).Value = "Yes"
> Case Else
> 'Set myRng = Target.Offset(0, -1).Resize(1, 1)
> 'myRng.Interior.ColorIndex = xlNone
> Set myRng = Target.Offset(0, 0).Resize(1, 1)
> myRng.Interior.ColorIndex = xlNone
> .Columns(2).Value = ""
> .Columns(3).Value = "-"
> End Select
> End If
> End With
> Case Else
> End Select
> End Sub
> *******************

Thanks Norman,

Unfortunatelly it doesn't work. When reaching # .Columns(2).Value =
(Now)". # the error kicks in and the colour isn't changed.

Any other suggestions?

TIA Rob

Hi Dqsp,

The suggested code works for me.

If you wish, I can send you a copy of my test
workbook, in reesponse to an email to:

norman_jones@NOSPAMbtconnectDOTcom


(Delete "NOSPAM" and replace "DOT" with a full stop [period] )


---
Regards.
Norman


"dqsp" <(E-Mail Removed)> wrote in message
news:1aa63c5d-0592-4cfd-aa0c-(E-Mail Removed)...
> Thanks Norman,
>
> Unfortunatelly it doesn't work. When reaching # .Columns(2).Value =
> (Now)". # the error kicks in and the colour isn't changed.
>
> Any other suggestions?
>
> TIA Rob

Hi Ddsp

Perhaps, more usefully, you could send
me a copy of your problematic workbook;
remove any sensitivr data.



---
Regards.
Norman


"Norman Jones" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Dqsp,
>
> The suggested code works for me.
>
> If you wish, I can send you a copy of my test
> workbook, in reesponse to an email to:
>
> norman_jones@NOSPAMbtconnectDOTcom
>
>
> (Delete "NOSPAM" and replace "DOT" with a full stop [period] )
>
>
> ---
> Regards.
> Norman

Hi Norman,

The workbook always worked fine for me as well, until recently.
It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I
tried the same workbook at home and it doesn't work either. I'm
sending it to your e-mailadres.
Thanks for helping!

Rob

On 29 mei, 19:59, "Norman Jones" <normanjo...@wherforartthou.com>
wrote:
> Hi Ddsp
>
> Perhaps, more usefully, you could send
> me a copy of your problematic workbook;
> remove any sensitivr data.
>
> ---
> Regards.
> Norman
>
> "Norman Jones" <normanjo...@wherforartthou.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > Hi Dqsp,
>
> > The suggested code works for me.
>
> > If you wish, I can send you a copy of my test
> > workbook, in reesponse to an email to:
>
> > * * * * norman_jones@NOSPAMbtconnectDOTcom
>
> > (Delete "NOSPAM" and replace "DOT" with a full stop [period] )
>
> > ---
> > Regards.
> > Norman- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Hi Rob,

I received your file and it works, as is,
straight out of the box!

I have sent you an updated file in which
I have done nothing other than
successfully to make entries after April
18 in column G.

Therefore, I suspect that you have the
Application events disabled. Perhaps
you have other code which is disabling
events and then failing to reinstate them.

FWIW, with any code which disables
Application settings, I always use an
error handler which reinstates the
required values.

I would suggest, therefore, two things:

- In the Immediate window (Ctrl-G from
the VBE), type:
Application.EnableEvents = True
and hit Enter;

- Check any other code in the workbook
(or any other workbooks which may be
opened) to ensure that any instruction
which disables events, includes an error
handler to restore the setting.



---
Regards.
Norman

"dqsp" <(E-Mail Removed)> wrote in message
news:0739ae21-c310-4cfc-917b-(E-Mail Removed)...
Hi Norman,

The workbook always worked fine for me as well, until recently.
It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I
tried the same workbook at home and it doesn't work either. I'm
sending it to your e-mailadres.
Thanks for helping!

Rob

On 29 mei, 19:59, "Norman Jones" <normanjo...@wherforartthou.com>
wrote:
> Hi Ddsp
>
> Perhaps, more usefully, you could send
> me a copy of your problematic workbook;
> remove any sensitivr data.
>
> ---
> Regards.
> Norman
>
> "Norman Jones" <normanjo...@wherforartthou.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > Hi Dqsp,
>
> > The suggested code works for me.
>
> > If you wish, I can send you a copy of my test
> > workbook, in reesponse to an email to:
>
> > norman_jones@NOSPAMbtconnectDOTcom
>
> > (Delete "NOSPAM" and replace "DOT" with a full stop [period] )
>
> > ---
> > Regards.
> > Norman- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

On 30 mei, 14:59, "Norman Jones" <normanjo...@wherforartthou.com>
wrote:
> Hi Rob,
>
> I received your file and it works, as is,
> straight out of the box!
>
> I have sent you an updated file in which
> I have done nothing other than
> successfully to make entries after April
> 18 in column G.
>
> Therefore, I suspect that you have the
> Application events disabled. Perhaps
> you have other code which is disabling
> events and then failing to reinstate them.
>
> FWIW, with any code which disables
> Application settings, I always use an
> error handler which reinstates the
> required values.
>
> I would suggest, therefore, two things:
>
> * * - In the Immediate window (Ctrl-G from
> * * * the VBE), type:
> * * * Application.EnableEvents = True
> * * * and hit Enter;
>
> * * - Check any other code in the workbook
> * * * (or any other workbooks which may be
> * * * opened) to ensure that any instruction
> * * * which *disables events, includes an error
> * * * handler to restore the setting.
>
> ---
> Regards.
> Norman
>
> "dqsp" <news4...@gmail.com> wrote in message
>
> news:0739ae21-c310-4cfc-917b-(E-Mail Removed)...
> Hi Norman,
>
> The workbook always worked fine for me as well, until recently.
> It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I
> tried the same workbook at home and it doesn't work either. I'm
> sending it to your e-mailadres.
> Thanks for helping!
>
> Rob
>
> On 29 mei, 19:59, "Norman Jones" <normanjo...@wherforartthou.com>
> wrote:
>
>
>
> > Hi Ddsp
>
> > Perhaps, more usefully, you could send
> > me a copy of your problematic workbook;
> > remove any sensitivr data.
>
> > ---
> > Regards.
> > Norman
>
> > "Norman Jones" <normanjo...@wherforartthou.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > Hi Dqsp,
>
> > > The suggested code works for me.
>
> > > If you wish, I can send you a copy of my test
> > > workbook, in reesponse to an email to:
>
> > > norman_jones@NOSPAMbtconnectDOTcom
>
> > > (Delete "NOSPAM" and replace "DOT" with a full stop [period] )
>
> > > ---
> > > Regards.
> > > Norman- Tekst uit oorspronkelijk bericht niet weergeven -
>
> > - Tekst uit oorspronkelijk bericht weergeven -- Tekst uit oorspronkelijkbericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Well, it's the only workbook opened and the only code used. So shoot
me, it really doesn't work after your suggested action in the
immediate window. At home I have the same problem with this.
In another workbook were I use simular code it also stopped working.
Could it be a general Excel setting?

TIA for your answer, really appreciate your actions!

Rob

Hi Rob,

Run the following sub and then try
changing column G values.

'========>>
Public Sub Tester()
Application.EnableEvents = True
End Sub
'<<========


---
Regards.
Norman


"dqsp" <(E-Mail Removed)> wrote in message
news:a4df477a-ffbd-4f00-a4ec-(E-Mail Removed)...
On 30 mei, 14:59, "Norman Jones" <normanjo...@wherforartthou.com>
wrote:
> Hi Rob,
>
> I received your file and it works, as is,
> straight out of the box!
>
> I have sent you an updated file in which
> I have done nothing other than
> successfully to make entries after April
> 18 in column G.
>
> Therefore, I suspect that you have the
> Application events disabled. Perhaps
> you have other code which is disabling
> events and then failing to reinstate them.
>
> FWIW, with any code which disables
> Application settings, I always use an
> error handler which reinstates the
> required values.
>
> I would suggest, therefore, two things:
>
> - In the Immediate window (Ctrl-G from
> the VBE), type:
> Application.EnableEvents = True
> and hit Enter;
>
> - Check any other code in the workbook
> (or any other workbooks which may be
> opened) to ensure that any instruction
> which disables events, includes an error
> handler to restore the setting.
>
> ---
> Regards.
> Norman
>
> "dqsp" <news4...@gmail.com> wrote in message
>
> news:0739ae21-c310-4cfc-917b-(E-Mail Removed)...
> Hi Norman,
>
> The workbook always worked fine for me as well, until recently.
> It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I
> tried the same workbook at home and it doesn't work either. I'm
> sending it to your e-mailadres.
> Thanks for helping!
>
> Rob
>
> On 29 mei, 19:59, "Norman Jones" <normanjo...@wherforartthou.com>
> wrote:
>
>
>
> > Hi Ddsp
>
> > Perhaps, more usefully, you could send
> > me a copy of your problematic workbook;
> > remove any sensitivr data.
>
> > ---
> > Regards.
> > Norman
>
> > "Norman Jones" <normanjo...@wherforartthou.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > Hi Dqsp,
>
> > > The suggested code works for me.
>
> > > If you wish, I can send you a copy of my test
> > > workbook, in reesponse to an email to:
>
> > > norman_jones@NOSPAMbtconnectDOTcom
>
> > > (Delete "NOSPAM" and replace "DOT" with a full stop [period] )
>
> > > ---
> > > Regards.
> > > Norman- Tekst uit oorspronkelijk bericht niet weergeven -
>
> > - Tekst uit oorspronkelijk bericht weergeven -- Tekst uit oorspronkelijk
> > bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Well, it's the only workbook opened and the only code used. So shoot
me, it really doesn't work after your suggested action in the
immediate window. At home I have the same problem with this.
In another workbook were I use simular code it also stopped working.
Could it be a general Excel setting?

TIA for your answer, really appreciate your actions!

Rob

Hi Norman,

I allready tried that, but no succes.
At home it works (had to do with macroprotection (grr)). At my work it
doesn't. If I debug with F8, I stll see that the macro ends
after .Columns(2).Value = (Now)

Any other idea?

Thanks again,
Rob

On 30 mei, 18:11, "Norman Jones" <normanjo...@wherforartthou.com>
wrote:
> Hi Rob,
>
> Run the following sub and then try
> changing column G values.
>
> '========>>
> Public Sub Tester()
> * * Application.EnableEvents = True
> End Sub
> '<<========
>
> ---
> Regards.
> Norman