Worksheet Event Problems

[Matthew Pfluger]

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

 

[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

 

[Dick Kusleika]

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.

 

[Matthew Pfluger]

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]

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?

 

[Matthew Pfluger]

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

 

[Dick Kusleika]

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.

(e-mail address removed)