Run macro after dropdown selection is made

[shane_aldrich]

Hi all...

I am creating a report compiler of sorts. There is a tab named Directions. After users hit a button a macro is ran, and the result is the user is prompted to make a selection from the dropdown list in Cell D15 of the Directions tab....

The entries in the dropdown will change all the time....

Regardless, after the selection is made, I would like the next macro to automatically run....(It will always be the same macro)

I tried running the macro based on a cell change, but for some reason that doesn't work with dropdowns...

And because the entries in the list changes, I don't know how to assign a macro to each entry

I hope this is clear...

 

[Smitty]

Welcome to the Board!

A Change event will respond to a Data Validation change.

From there you can use a Select Case structure to test which value has been selected.

Here's a boilerplate example:

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

Hope that helps,

 

[shane_aldrich]

I admit to being a VBA dullard, I tried using what you posted but am doing something wrong, so I created an example

The file is named - Sample
There are 2 tabs - Directions, Raw1

Using Excel 2007 I posted this in the "ThisWorkbook" section
______________________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("Directions!D15")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
Application.Run "Sample.xls!Macro1"
End Sub
_______________________________________________________________
In Module1 I have....
_______________________________________________________________
Sub Macro1()
' Macro1 Macro
Sheets("Raw1").Select
Range("A1").Formula = "=1+1"
Range("A2").Formula = "=2+2"
End Sub
______________________________________________________________


Cell D15 on the Directions Tab is a dropdown based on cell validation...

They are in a YYYY-MM format, but the dates will change based on the raw info...

2009-11
2009-10
2009-09

 

[Smitty]

Try referring to the target reference like this:

Set rng = Target.Parent.Range("D15")

You don't need the sheet name, as it's a sheet level event, so the VBE knows where it is. And the correct sheet/range referecence for other things in VBA would be like this:

Sheets("Directions").Range("D15")

It's different than a formula based reference (unless you're using the Goto Reference method).

On a side note, I find that adding MsgBox "something" to events you expect to happen is a good testing method.