Error Handling for Data Validation List

[mikeyank]

Help me, good people!

I have a excel document that is very heavy with Data Validation List Boxes. Below is just one example. Error handling is what I am trying to figure out.
Description:
I have 3 List Boxes.
One list box data is based on data selected in the previous list box.

1st List Box uses this formula: =Series
2nd List Box uses this formula: =INDIRECT($C$2)
3rd List Box uses this formula: =INDIRECT($C$3)

It all works! HOWEVER....
If user decides to go back and change selection in first or second list box, and forgets to change the data in the second or third box, then data will be incorrect. How do I solve this problem?
Is there anyway to write something so it would give them an error message and instructing them to correct the data?

Thanks

[jbeaucaire]

I use worksheet_change macros to force changes. Basically, every time C2 is changed, the dependent boxes clear. If C3 changes, C4 is cleared. Right-click on the sheet tab, choose VIEW CODE and insert this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("C2")) Is Nothing Then
        Application.EnableEvents = False
        Range("C3:C4").ClearContents
        Application.EnableEvents = True
    End If
    
    If Not Intersect(Target, Range("C3")) Is Nothing Then
        Application.EnableEvents = False
        Range("C4").ClearContents
        Application.EnableEvents = True
    End If
    
End Sub

[mikeyank]

That would be awesome! However, please help me troubleshoot this error.
The cells that handle the validation are merged cells. Therefore when I change the data, I get this error:
Run-time error '1004':
Cannot change part of merged cell.

[lenze]

That would be awesome! However, please help me troubleshoot this error.
The cells that handle the validation are merged cells. Therefore when I change the data, I get this error:
Run-time error '1004':
Cannot change part of merged cell.

VBA hates merged cells. Try using "Center Across Selection" instead.

lenze

[mikeyank]

I am not sure if "Center Across Selection" is available in Excel 2002.
And, I really need them merged for the rest of the form.

[mikeyank]

Another strange thing...
After I got this error, it does not come up anymore, BUT the code still does not work. NOTHING HAPPENS!
CONFUSED!

[jbeaucaire]

Sorry, that's because the macro disabledevents...then errored out. You need to
go into the VBEditor, press Crtrl-G to get to the Immediate window, and put in this command to turn it back on:

Application.EnableEvents = True

[mikeyank]

Oh, great! That worked!
Do you have any idea how to make it work with mergd cells now!

BTW, your site is great! Makes me wanna get into your hubby!

[jbeaucaire]

Here's a corrected macro that will fix itself after an error:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler

    If Not Intersect(Target, Range("C2")) Is Nothing Then
        Application.EnableEvents = False
            Range("C3:C4").ClearContents
    End If
    
    If Not Intersect(Target, Range("C3")) Is Nothing Then
        Application.EnableEvents = False
            Range("C4").ClearContents
    End If

Reset_all:
   Application.ScreenUpdating = True
   Application.EnableEvents = True
   Exit Sub

ErrorHandler:
    MsgBox Err.Number & " - " & Erl & " - " & Err.Description
    Resume Reset_all
End Sub

To make it work on merged cells, unmerge them and fall out of love with that feature.

[mikeyank]

...To make it work on merged cells, unmerge them and fall out of love with that feature.

I wish I could . However. it throws all my formatting off.
I have drop downs all of different sizes. Some rows have 2 long ones.
Some have 5 short ones.
Worst of all it was provided by client of ours, and I have to make it work.