I'm trying to write advanced Data Validation using VBA instead of the
built-in Data Validation because I need a dependent cell to change its value
if an independent cell's value changes (the whole dependent list problem
again). I'm using Worksheet_Change to accomplish this. However, the
following code gives me an error.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 0
Stop
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Range("inpSplineFit").value = "FILLET ROOT SIDE FIT"

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
end sub

Range("inpSplineFit") = Cell B5, and the name is valid and not the cause of
the problem. The only way to get the error code is to step through the code
because it doesn't display any message by itself. It just errors out and
keeps moving, despite the error handling. The error is 40040,
Application-defined or object-defined error. The worksheet is not protected.
Even more strangely, the exact same code works when used in
Worksheet_SelectionChange.

I've tried deleting the sheet and starting over. I've tried cleaning the
project with Code Cleaner, and nothing works. What is going on?

Thanks,
Matthew Pfluger

I should note that the cell I mentioned below is number two in a series of a
parent list and 4 dependent lists. Each cell uses a named range as its list
source, and each source looks something like this:
"=INDIRECT(ptrLookupList)"
where "ptrLookupList" would be a cell on another worksheet that contains a
formula that returns a string representing a named range.

Again, the macros work with Worksheet_SelectionChange and _Calculate, but
not with Change. In fact, the SelectionChange and Calculate events allow me
to click on other cells as I step through the code, but Change does not.
I've also tried forcing a recalculation before any change occurs, but that
didn't work.

Any ideas? Thanks.

Matthew Pfluger

"Matthew Pfluger" wrote:

I'm trying to write advanced Data Validation using VBA instead of the
built-in Data Validation because I need a dependent cell to change its value
if an independent cell's value changes (the whole dependent list problem
again). I'm using Worksheet_Change to accomplish this. However, the
following code gives me an error.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 0
Stop
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Range("inpSplineFit").value = "FILLET ROOT SIDE FIT"

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
end sub

Range("inpSplineFit") = Cell B5, and the name is valid and not the cause of
the problem. The only way to get the error code is to step through the code
because it doesn't display any message by itself. It just errors out and
keeps moving, despite the error handling. The error is 40040,
Application-defined or object-defined error. The worksheet is not protected.
Even more strangely, the exact same code works when used in
Worksheet_SelectionChange.

I've tried deleting the sheet and starting over. I've tried cleaning the
project with Code Cleaner, and nothing works. What is going on?

Thanks,
Matthew Pfluger

On Mon, 4 Aug 2008 13:40:11 -0700, Matthew Pfluger
wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 0
Stop
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Range("inpSplineFit").value = "FILLET ROOT SIDE FIT"

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
end sub

Range("inpSplineFit") = Cell B5, and the name is valid and not the cause of
the problem. The only way to get the error code is to step through the code
because it doesn't display any message by itself. It just errors out and
keeps moving, despite the error handling. The error is 40040,
Application-defined or object-defined error. The worksheet is not protected.
Even more strangely, the exact same code works when used in
Worksheet_SelectionChange.

Try changing that line to

Me.Range("inpSplineFit").Value = "FILLET ROOT SIDE FIT"

I'm not sure what's happening, but that error is typical of trying to
address a range that's on a different sheet.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Hi Dick,

I changed the Change event to a Calculate event, and things worked.
However, I was hoping to only have the event run when those particular 5
cells changed. I think the problem is that the 4 dependent cells have data
validation formulas, and those need to be recalculated once the other cells
change. Since the Change event happens before Calculation and the Calculate
event, I think Excel doesn't know what to do. It won't let me change any of
the 5 cells during a Change event triggered by one of those 5 cells.

So, I'm not sure what else to do but use the Calculate event. I know it's
not a huge deal, but the event clears the clipboard, and it does this at each
calculation. Rather than have a user get upset at not being able to
customize the sheet (form), I instead locked everything else except those 5
cells.

Thanks,
Matthew Pfluger

"Dick Kusleika" wrote:

On Mon, 4 Aug 2008 13:40:11 -0700, Matthew Pfluger
wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 0
Stop
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Range("inpSplineFit").value = "FILLET ROOT SIDE FIT"

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
end sub

Range("inpSplineFit") = Cell B5, and the name is valid and not the cause of
the problem. The only way to get the error code is to step through the code
because it doesn't display any message by itself. It just errors out and
keeps moving, despite the error handling. The error is 40040,
Application-defined or object-defined error. The worksheet is not protected.
Even more strangely, the exact same code works when used in
Worksheet_SelectionChange.

Try changing that line to

Me.Range("inpSplineFit").Value = "FILLET ROOT SIDE FIT"

I'm not sure what's happening, but that error is typical of trying to
address a range that's on a different sheet.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

On Mon, 4 Aug 2008 16:51:01 -0700, Matthew Pfluger
wrote:

Hi Dick,

I changed the Change event to a Calculate event, and things worked.
However, I was hoping to only have the event run when those particular 5
cells changed. I think the problem is that the 4 dependent cells have data
validation formulas, and those need to be recalculated once the other cells
change. Since the Change event happens before Calculation and the Calculate
event, I think Excel doesn't know what to do. It won't let me change any of
the 5 cells during a Change event triggered by one of those 5 cells.

So, I'm not sure what else to do but use the Calculate event. I know it's
not a huge deal, but the event clears the clipboard, and it does this at each
calculation. Rather than have a user get upset at not being able to
customize the sheet (form), I instead locked everything else except those 5
cells.


Can you tell me what the validation formulas are, which cells the
validations are in, and any range name definitions?
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Do you have an email address I can forward the file to (perhaps through
dailydoseofexcel.com)? It will be easier to show you that way than describe
it. It's a pretty strange error, and I'm not sure why it's happening. I
even tried to put a CalculateFullRebuild in the _Change Event, and that
didn't work.

Matthew Pfluger

On Tue, 5 Aug 2008 10:59:01 -0700, Matthew Pfluger
wrote:

Do you have an email address I can forward the file to (perhaps through
dailydoseofexcel.com)? It will be easier to show you that way than describe
it. It's a pretty strange error, and I'm not sure why it's happening. I
even tried to put a CalculateFullRebuild in the _Change Event, and that
didn't work.



--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com