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]

VBA hates merged cells.
You have to use merged cells.

Who wins?

 

[lenze]

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.

The reason is, Events were disabled before the error and not turned back on. In the VBA, choose View>Immediate Window and enter
Application.EnableEvents = True

lenze

 

[gecs]

Using .Value = "" instead of .ClearContents will not trigger an error related to merged cells.

Also, what is the purpose of disabling events before changing the value of those cells? Instead of triggering the Change event only once, it will be triggered 2 or 3 times - is this a problem? In my opinion is not a problem, but an advantage because the code can be shorter:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 2 Then
        If Target.Column = 3 Then
            Cells(3, 3).Value = ""
        End If
    Else
        If Target.Row = 3 Then
            If Target.Column = 3 Then
                Cells(4, 3).Value = ""
            End If
        End If
    End If
End Sub

 

[jbeaucaire]

"A+" for the .Value approach. Great tip.
========
On the other hand, "short code" is a personal aesthetic and not the best basis (IMO) for selecting a VBA approach. I can write a loop in 3-lines of code to evaluate and copy data from a 50,000 line set of data and it can take FAR longer to execute than a 10-line approach.

I'm just saying, you're right, having the event code triggered 2-3 times isn't a "Problem", it's just not a practice I could ever advocate...especially not for the sake of "shorter code".

My two cents.

Original code adjusted to include the .Value method:

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").Value = ""
    End If
    
    If Not Intersect(Target, Range("C3")) Is Nothing Then
        Application.EnableEvents = False
            Range("C4").Value = ""
    End If

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

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

 

[gecs]

"Short code" wasn't the best expression I could use (my English wasn't good enough then ), but I thought it would be easy to notice the advantage from allowing the cascading change event to happen in this situation. Even if I'm not a proffessional programmer, I can understand the importance of "disabling" certain events sometimes to prevent collateral undesired effects, but here is not the case, in my opinion. On the contrary. If you look at my code you'll notice that in the first IF sequence only the value of the second level of the hierarchical data structure is changed to "", not for both level 2 and 3, because the cascading event does the rest. Actually this was the reason for that "shorter code" expression I used.

 

[lenze]

I seem to recall that Bill offered a prize for the 1,00.,00th post. This caused a lot of activity as it neared as people were watching the post count trying to be the 1,000,000th post. Yea, but Greg and Richard were out manuvered by the winner, DominicB.
<a href="http://www.mrexcel.com/forum/showthread.php?t=188007">1,000,000</a>

lenze

 

[mikeyank]

jbeaucaire,

I went ahead and redesigned my worksheet not to use merged cells. Your code now works perfect! I am soooooo greatful! Thank you so much for your time!

 

[copaymetosurfnet]

Hi,
I copied the code below & followed the steps given, later changed the field range to my worksheet field range, but it doesn't work for me.

my 1st validation list is on cell Q2 (COLLECT, PREPAID)

2nd dependant validation list is on cell Q3 (which depend on Q2 answer)

IF Q2 =COLLECT then Q3 = FOB SINGAPORE (cell named "COLLECT")

if Q2 = PREPAID then Q3 either - "CIF " &dest_port or "CFR " &dest_port
(both cells named "PREPAID")


After selected cell Q2, then cell Q3, i go & test to change cell Q2, but the cell Q3 DOES NOT change (clear) even i have copied the suggested code below to 'Developer tab-view code-(left :worksheet)' -i am using excel 2007.


Your code as follows :



Option ExplicitPrivate 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




</PRE>
I changed to
Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
''http://www.mrexcel.com/forum/showthread.php?t=397608

Application.EnableEvents = True

If Not Intersect(Target, Range("Q2")) Is Nothing Then
Application.EnableEvents = False
Range("Q3:Q3").ClearContents
Application.EnableEvents = True
End If


End Sub






</PRE>I am not an expert for what so call 'event' nor VBA code, but following your step is somehow easy, so i wonder where goes wrong. What step i did wrongly ? i am very happy when i learn of this "INDIRECT()" function but now there still the above problem which i need to figure out, if not, would not put this function in the validation list.

Pls help if you could. TQ in advance.

 

[StuLux]

I have come across this before with merged cells - instead of trying to clear contents try making the value ="" as below:

Private Sub Worksheet_Change(ByVal Target As Range)
''http://www.mrexcel.com/forum/showthread.php?t=397608
Application.EnableEvents = True
If Not Intersect(Target, Range("Q2")) Is Nothing Then
Application.EnableEvents = False
Range("Q3").Value = ""
Application.EnableEvents = True
End If
 
End Sub

 

[copaymetosurfnet]

Hi StuLux of Cardiff, UK,

Thank you very much for your code. It did for me. Thank you. Really appreciate your kindness of helping me. TQ .