Event Change Question

[Tcarey]

I have 5 depenant drop down lists all stacked on top of each other. They work in that order, top to bottom. If the user works his way down through the list to the final selection, but wants to maybe reselect at the 2 or 3rd box I'd like it to only clear those below. How do I try to accomplish this task?

 

[Tcarey]

I've tried using a did cell change function, but changing changing a selection in the dependant list does not trigger it...

 

[Smitty]

A Change event will respond to a Validation selection. Here's some boilerplate code:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[Tcarey]

Thanks, I'll see what I can do with it.

 

[Tcarey]

Ok, how the hell do I delete a range without deleting the depenant list.

I don't know of another clear/delete function

Range("d13:d14").Select
         Selection.Delete
         Range("A3").Select

 

[Smitty]

Range("D13:D14").ClearContents

 

[Tcarey]

Must be nice

 

[Tcarey]

I was under the impression it would be possible to use this same code and edit the target range and clear ranges to make it clear the boxs under the 3rd and 4th selections. However, it gives me the ambigious name error.

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("D10")
        '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
        '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
        '   Action if Condition(s) are met (do your thing here...)
           'page 1 delete rows
         
        'page 1 delete rows
         Range("D13:D14").ClearContents
         Range("D16:D17").ClearContents
         Range("D19:D20").ClearContents
         
         End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("D13")
        '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
        '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
        '   Action if Condition(s) are met (do your thing here...)
           'page 1 delete rows
         
        'page 1 delete rows
         
         Range("D16:D17").ClearContents
         Range("D19:D20").ClearContents
         
         End Sub

 

[Smitty]

Right, you can't have more than one of any event otherwise they'll conflict because VBA doesn't know which one to compile.

You need to combine them, then you can use a select case statement to perform different actions on different target ranges if need be.

 

[Tcarey]

I'm not sure how to do that..Do you know where I can find some reading?