Worksheet_Change after validation

[DoctorG]

I set up a Worksheet_Change event so as to change the background color of a
row relevant to the value of the column C cell (True/False). It works after
manually changing the values in Column C.

Following ND Pard's advice I managed to set up Data Validation in the C
Column cells so as to restrict entry to True/False. This works.

The problem is that InCell DropDown Validation does not seem to trigger the
Worksheet_Change event and the row background color does not reflect the
value change anymore.

Is this standard behaviour or am I doing something wrong? If it is standard,
where should I code the background change mechanism so that it works?

 

[Bernie Deitrick]

Doctor G,

The change event is triggered by choosing a value from the Data Validation dropdown.

Make sure that you haven't disabled events accidentally:

Sub ResetEvents()

Application.EnableEvents = True

End Sub

 

[DoctorG]

Bernie thanks for your answer but it's not the Enable Events thing. I tried
it, it wasn't disabled anyway, but nothing changed.

Here's what I have that is not working (I am checking if i am in a Column C
cell and it has been changed - then I shade or clear the two cells to the
left - Columns A & B). I am afraid that the ActiveCell approach creates
problems.

prow = ActiveCell.Row
pcol = ActiveCell.Column

If pcol = 3 Then
If ActiveCell.Value = True Then ActiveCell.Offset(0, -1).Interior.Color =
xlNone
If ActiveCell.Value = True Then ActiveCell.Offset(0, -2).Interior.Color =
xlNone
If ActiveCell.Value = False Then ActiveCell.Offset(0, -1).Interior.Color
= RGB(128, 128, 128)
If ActiveCell.Value = False Then ActiveCell.Offset(0, -2).Interior.Color
= RGB(128, 128, 128)
End If

 

[Rick Rothstein]

Actually, I get your code to work properly in a Change event procedure;
however, the clearing of the interior color isn't done correctly using the
Color property. See if this code works (make sure you are putting it into
the correct Worksheet's code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveCell
If .Column = 3 Then
If .Value = True Then
.Offset(0, -2).Resize(1, 2).Interior.ColorIndex = xlNone
Else
.Offset(0, -2).Resize(1, 2).Interior.Color = RGB(128, 128, 128)
End If
End If
End With
End Sub

 

[DoctorG]

Rick excuse me for not getting back to this sooner.

I did some tests and found out where the error takes place - but I don't
know why or how to correct it. Here's the deal.

1. I tried your solution and it didn't work. It only works, as mine does, if
I enter the desired value in the cell manually and press enter.
2. I closed the spreadsheet and tried only the proposed solution in a brand
new spreadsheet. This works flawlessly.
3. Having the new file open, I re-opened my original spreadsheet and both
the original as the new file exhibit the same behaviour. Formatting doesn't
take place not even in the new file.

Therefore my original spreadsheet "does" something to the environment that
affects every open spreadsheet.

After working with the debugger I found the following to take place, which I
hope you can figure out.

[I put a break in the first line after the "If .column =3 then" so that I
can watch what happens.]

The "If .Value = True then" works for both TRUE and FALSE values.

** BUT **

As soon as I hit F8 in the ".Offset(0, -2).Resize(1, 2).Interior.ColorIndex
= xlNone" statement
(A) The yellow execution line pointer disappears
(B) The locals window in the debugger blanks out
(C) The execution obviously halts and control returns to the spreadsheet

Yet, if I manually press F2 and ENTER in the same cell as I was before and
Worksheet_Change execution halted, I get the same break in the debugger,
execution continues after the afore mentioned F8, interior coloring is
performed and the event runs past the EndIfs and closes normally.

I am aware that you cannot reproduce this behaviour.

Can you imagine what could stop the execution of the routine if it is
initiated by an InCell Data Validation DropDown Selection "path" whereas
nothing goes wrong after a direct (manual) value entry?
Is there a way to get some information from the debugger as to what goes on
when I hit F8 and command execution stops? Mind you that the actual command
is not executed. Termination takes place BEFORE execution - that's why the
cells don't become grey.

Thanks a lot in advance for any effort you make. I am at the end of my rope
with this one.

 

[Rick Rothstein]

I'm really not sure what to tell you. I'm guessing you have other code
running somewhere and it is doing something on a global scale, but it is
hard for me to diagnose something like that. While it is possible that this
code that is screwing things up could be located outside of your workbook
(in your Personal sheet if you have one or in some other automatically
loaded code), that is probably not the case... I'm guessing it is code
running in your workbook (maybe the Workbook_Open event). I don't expect
this to work, but give it a try anyway... change the object of the With
statement from ActiveCell to Target (be careful to only select one cell for
now... if this works, I'll give you some additional protection code for
multiple cell selections). If that doesn't work (and I doubt that it will),
I would be willing to look at your workbook if send it to me (remove the
NO.SPAM stuff from my address), but better would be if you could post it
online so others (more experienced with these kinds of things than I am)
could look at it too. Let me know what you decide (if you send it to me,
I'll need to allow it through my spam filter).

--
Rick (MVP - Excel)

Rick excuse me for not getting back to this sooner.

I did some tests and found out where the error takes place - but I don't
know why or how to correct it. Here's the deal.

1. I tried your solution and it didn't work. It only works, as mine does,
if
I enter the desired value in the cell manually and press enter.
2. I closed the spreadsheet and tried only the proposed solution in a
brand
new spreadsheet. This works flawlessly.
3. Having the new file open, I re-opened my original spreadsheet and both
the original as the new file exhibit the same behaviour. Formatting
doesn't
take place not even in the new file.

Therefore my original spreadsheet "does" something to the environment that
affects every open spreadsheet.

After working with the debugger I found the following to take place, which
I
hope you can figure out.

[I put a break in the first line after the "If .column =3 then" so that I
can watch what happens.]

The "If .Value = True then" works for both TRUE and FALSE values.

** BUT **

As soon as I hit F8 in the ".Offset(0, -2).Resize(1,
2).Interior.ColorIndex
= xlNone" statement
(A) The yellow execution line pointer disappears
(B) The locals window in the debugger blanks out
(C) The execution obviously halts and control returns to the spreadsheet

Yet, if I manually press F2 and ENTER in the same cell as I was before and
Worksheet_Change execution halted, I get the same break in the debugger,
execution continues after the afore mentioned F8, interior coloring is
performed and the event runs past the EndIfs and closes normally.

I am aware that you cannot reproduce this behaviour.

Can you imagine what could stop the execution of the routine if it is
initiated by an InCell Data Validation DropDown Selection "path" whereas
nothing goes wrong after a direct (manual) value entry?
Is there a way to get some information from the debugger as to what goes
on
when I hit F8 and command execution stops? Mind you that the actual
command
is not executed. Termination takes place BEFORE execution - that's why the
cells don't become grey.

Thanks a lot in advance for any effort you make. I am at the end of my
rope
with this one.

Actually, I get your code to work properly in a Change event procedure;
however, the clearing of the interior color isn't done correctly using
the
Color property. See if this code works (make sure you are putting it into
the correct Worksheet's code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveCell
If .Column = 3 Then
If .Value = True Then
.Offset(0, -2).Resize(1, 2).Interior.ColorIndex = xlNone
Else
.Offset(0, -2).Resize(1, 2).Interior.Color = RGB(128, 128, 128)
End If
End If
End With
End Sub

 

[DoctorG]

Thanks a lot Rick for bothering, anyway. I will give it a try and see how it
goes. I will also try deleting worksheets and code and see where something
changes. Something outside of your instructions but inside my worksheet is
messing things up. It is definitely not global, otherwise the empty (new)
spreadsheet wouldn't work - right?

I'll keep you posted. Whatever I discover will continue this thread. If it
has to come to that I will post my spreadsheet as you suggest.

Thanks again. Bye for now - it's debuging time!

I'm really not sure what to tell you. I'm guessing you have other code
running somewhere and it is doing something on a global scale, but it is
hard for me to diagnose something like that. While it is possible that this
code that is screwing things up could be located outside of your workbook
(in your Personal sheet if you have one or in some other automatically
loaded code), that is probably not the case... I'm guessing it is code
running in your workbook (maybe the Workbook_Open event). I don't expect
this to work, but give it a try anyway... change the object of the With
statement from ActiveCell to Target (be careful to only select one cell for
now... if this works, I'll give you some additional protection code for
multiple cell selections). If that doesn't work (and I doubt that it will),
I would be willing to look at your workbook if send it to me (remove the
NO.SPAM stuff from my address), but better would be if you could post it
online so others (more experienced with these kinds of things than I am)
could look at it too. Let me know what you decide (if you send it to me,
I'll need to allow it through my spam filter).

--
Rick (MVP - Excel)

Rick excuse me for not getting back to this sooner.

I did some tests and found out where the error takes place - but I don't
know why or how to correct it. Here's the deal.

1. I tried your solution and it didn't work. It only works, as mine does,
if
I enter the desired value in the cell manually and press enter.
2. I closed the spreadsheet and tried only the proposed solution in a
brand
new spreadsheet. This works flawlessly.
3. Having the new file open, I re-opened my original spreadsheet and both
the original as the new file exhibit the same behaviour. Formatting
doesn't
take place not even in the new file.

Therefore my original spreadsheet "does" something to the environment that
affects every open spreadsheet.

After working with the debugger I found the following to take place, which
I
hope you can figure out.

[I put a break in the first line after the "If .column =3 then" so that I
can watch what happens.]

The "If .Value = True then" works for both TRUE and FALSE values.

** BUT **

As soon as I hit F8 in the ".Offset(0, -2).Resize(1,
2).Interior.ColorIndex
= xlNone" statement
(A) The yellow execution line pointer disappears
(B) The locals window in the debugger blanks out
(C) The execution obviously halts and control returns to the spreadsheet

Yet, if I manually press F2 and ENTER in the same cell as I was before and
Worksheet_Change execution halted, I get the same break in the debugger,
execution continues after the afore mentioned F8, interior coloring is
performed and the event runs past the EndIfs and closes normally.

I am aware that you cannot reproduce this behaviour.

Can you imagine what could stop the execution of the routine if it is
initiated by an InCell Data Validation DropDown Selection "path" whereas
nothing goes wrong after a direct (manual) value entry?
Is there a way to get some information from the debugger as to what goes
on
when I hit F8 and command execution stops? Mind you that the actual
command
is not executed. Termination takes place BEFORE execution - that's why the
cells don't become grey.

Thanks a lot in advance for any effort you make. I am at the end of my
rope
with this one.

Actually, I get your code to work properly in a Change event procedure;
however, the clearing of the interior color isn't done correctly using
the
Color property. See if this code works (make sure you are putting it into
the correct Worksheet's code window)...

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveCell
If .Column = 3 Then
If .Value = True Then
.Offset(0, -2).Resize(1, 2).Interior.ColorIndex = xlNone
Else
.Offset(0, -2).Resize(1, 2).Interior.Color = RGB(128, 128, 128)
End If
End If
End With
End Sub

--
Rick (MVP - Excel)


Bernie thanks for your answer but it's not the Enable Events thing. I
tried
it, it wasn't disabled anyway, but nothing changed.

Here's what I have that is not working (I am checking if i am in a
Column
C
cell and it has been changed - then I shade or clear the two cells to
the
left - Columns A & B). I am afraid that the ActiveCell approach creates
problems.

prow = ActiveCell.Row
pcol = ActiveCell.Column

If pcol = 3 Then
If ActiveCell.Value = True Then
ActiveCell.Offset(0, -1).Interior.Color
=
xlNone
If ActiveCell.Value = True Then
ActiveCell.Offset(0, -2).Interior.Color
=
xlNone
If ActiveCell.Value = False Then
ActiveCell.Offset(0, -1).Interior.Color
= RGB(128, 128, 128)
If ActiveCell.Value = False Then
ActiveCell.Offset(0, -2).Interior.Color
= RGB(128, 128, 128)
End If

:

Doctor G,

The change event is triggered by choosing a value from the Data
Validation dropdown.

Make sure that you haven't disabled events accidentally:

Sub ResetEvents()

Application.EnableEvents = True

End Sub


--
HTH,
Bernie
MS Excel MVP


I set up a Worksheet_Change event so as to change the background
color
of a
row relevant to the value of the column C cell (True/False). It
works
after
manually changing the values in Column C.

Following ND Pard's advice I managed to set up Data Validation in
the C
Column cells so as to restrict entry to True/False. This works.

The problem is that InCell DropDown Validation does not seem to
trigger
the
Worksheet_Change event and the row background color does not reflect
the
value change anymore.

Is this standard behaviour or am I doing something wrong? If it is
standard,
where should I code the background change mechanism so that it
works?

 

[DoctorG]

Hi Rick,

Problem cause found - Explanation missing - Solution unknown

I have coded a function to calculate the Tax Payable amount from a Taxed
Amount and relevant percentages. This Function is placed in Module1 and is
used in a Worksheet (i.e. Sheet1) other than the one we have been discussing
(i.e. Sheet2).

A) If I delete the 8 cells where this function is used in Sheet1, our code
works fine in Sheet2.
B) If I use this Function in Sheet1 our code fails in Sheet2.
C) If I use this Function in Sheet2 but not in Sheet1, OUR CODE WORKS in
Sheet2.

How does this sound? What can you make of it?

I am thinking that maybe it has to do with the fact that the
Worksheet_Change Event is Private and it takes place in Sheet2 whereas the
Function refreshes the value of a cell in Sheet1 and focus is lost.

******************************************************************************************

[Modules => Module1]

Option Explicit

Function TaxPayable(TaxYear As Integer, TaxedAmount As Double) As Double

Static TaxLimit As Variant, TaxPercentage As Variant
Dim RestAmount As Double, CalcAmount As Double, TaxAmount As Double, jj As
Integer

If TaxedAmount < 0 Then
TaxAmount = 0
Else
If TaxYear < 2008 Then
TaxAmount = -1
Else
TaxLimit = Array(10500, 19500, 30000, 9999999)
TaxPercentage = Array(10, 27, 37, 45)
RestAmount = TaxedAmount
CalcAmount = 0
TaxAmount = 0
For jj = 0 To 3
CalcAmount = Application.WorksheetFunction.Min(TaxLimit(jj),
RestAmount)
TaxAmount = TaxAmount + CalcAmount * TaxPercentage(jj) / 100
RestAmount = RestAmount - CalcAmount
Next
End If
End If

TaxPayable = TaxAmount

End Function