Colours not changing

[dqsp]

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
*******************

 

[Norman Jones]

Hi Dqsp,

Near the head of your procedure, you
should change:

Application.EnableEvents = True
to


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.

 

[dqsp]

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

 

[Norman Jones]

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] )

 

[Norman Jones]

Hi Ddsp

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

 

[dqsp]

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

Hi Ddsp

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

---
Regards.
Norman

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] )

- Tekst uit oorspronkelijk bericht weergeven -

 

[Norman Jones]

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

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

Hi Ddsp

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

---
Regards.
Norman

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] )

- Tekst uit oorspronkelijk bericht weergeven -

 

[dqsp]

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


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

Hi Ddsp

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

news:[email protected]...

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] )

- 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

 

[Norman Jones]

Hi Rob,

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

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


---
Regards.
Norman

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


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

Hi Ddsp

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

news:[email protected]...

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] )

- 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

 

[dqsp]

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

 

[Norman Jones]

Hi Rob,

Earlier, you reported that the code
was not working either on your
home or work systems; now, at
least, works on your home system.

Reviwing the thread, I note that
you only discuss the 'error' in
terms of stepping though the code.

Therefore, two questions arise:

What happens when remove any
stop points and simply select a new
value from one of the data
validation dropdowns in column G?

What error message and error
number are reported?

If the error complains about the
Now function, I would suggest that
you check the references:

When the code errors, go to the
VBE and select the project in the
Project Explorer Window

Hit the reset button and look at the
Menu | Tools | References list

Look for any references marked
as "MISSING".

Either uncheck the reference or, if
the reference is needed, try the browse
button to locate the library.




---
Regards.
Norman


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

 

[dqsp]

Hi Norman,

I only stepped through the code to find the errorspot. The macro just
stops, no errormessage from Excel. I added some code to produce a
msgbox with the errorcode and the error is a "1004 Application or
object defined error", which doesn't surprise me.
No references missing.
I replaced the (now) with "01-06-2008" (Dutch format). Same errorcode.
When I delete the two lines for the values for column 2 and 3, no
error anymore, but the cell isn't colored blue (for example).
When I delete only the columns(2) line, the same error occurs.
Dark forces at work if you ask me!

Still any ideas?

Thanks again,
regards,
Rob

 

[dqsp]

Oh, and when I use (now) in the 'Case else' statement instead of "" in
the columns(2) line, todays date is presented in the cell.