macro executed by drop-down list. how?

[waldymar]

Hi,
I have a macro which output depence on the variable input. Input is represented by drop-down list. Before, I was choosing input from drop-down, then pressing the button my macro was running. Now, I'm thinking to if it's possible to exclude a spare operation from button pressing, just by clicking from drop-down. Any ideas?
My drop-down list is created through "Data->Validation->List box"

 

[Smitty]

Yes, you can do it with a change event:

<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</SPAN><br>            <SPAN style="color:#007F00">'   Do your thing here</SPAN><br>            <SPAN style="color:#00007F">Call</SPAN> MacroName<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[waldymar]

Probably, you misunderstood me. I have only 1 drop-down list. It looks very simple. From that drop-down I choose a value and want my macro runs. So, how to make my macro running just by choosing a value from drop-down?

 

[waldymar]

Smitty thanks,
you were wright, your code is exactly what I was looking for!!!
many thanks

 

[lenze]

I don't know that you need it, but you can use Smitty's code to run different macros depending on the choice in the DV cell

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("xxx")
             '   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
            Select Case Target
                 Case "Text 1" : Call Macro1
                 Case "Text 2" : Call Macro2
                 'etc
                 Case Else:
             End Select
End Sub

lenze

 

[waldymar]

lenze, you started, man, so I will use my opportunity to torture you a bit. to become familiar with worksheet_change property.
In your code:
1. what does it stand for -.Parent.-?
2. saying "If Target.Count > 1 Then Exit Sub", you mean that macro should run only if the change happened in my single cell and not in the range of the cells?
3. what you want to say by this: Intersect(Target, rng)?

 

[lenze]

Actrually, ".Parent" and Intersect(Target, rng) are my friend Smiity's approach. I would do it another (shorter) way, depending on needs, but I'm lazy. Smitty's way is probably technically better.

If Target.Count > 1 simply prevents the code from running if more than 1 cell is changed at a time. This might be clearing contents of several cells at once

If you explain your exact needs, I'm sure one of us can set you straight.

lenze

 

[Smitty]

Actually Tom Urtis pointed out using Target.Parent.Range...It's just a way of ensuring the code is looking in the right place.

If Intersect(Target, rng) Is Nothing Then Exit Sub

Just says that if the target cell and the range you define for the event to work on aren't in the same range, then to exit the sub. It's a way of telling the code where you want it to work, and limit it from working on the entire sheet.

 

[waldymar]

If Target.Count > 1 simply prevents the code from running if more than 1 cell is changed at a time. This might be clearing contents of several cells at once.
lenze

So, if I clear a content in 1 cell the macro is running?

If Intersect(Target, rng) Is Nothing Then Exit Sub

Just says that if the target cell and the range you define for the event to work on aren't in the same range, then to exit the sub. It's a way of telling the code where you want it to work, and limit it from working on the entire sheet.

Is it possible that the target cell and the range I define are not in the same range? before we state that "Set rng = Target.Parent.Range("xxx")" ...

 

[waldymar]

Basically, what does "Target" property stands for? I thought it's a cell I want to change and which will lead to the execution of my macro ... Or not?