Running a macro from a drop down menu

[davemc2016]

I'm fairly new to the world of macros but i have managed to set up some simple macros that filter some data by using the "Adavanced Filter" function. This data is filtered to show whether a set of chemical standards are going to expire "within 2 weeks", "within 1 month" etc etc. These have all been set up using buttons (5 buttons in total) but i've been asked to filter the data again on method (30 odd methods) and the screen is going to look clutted and unusuable. So i'm trying to put in a drop down menu so that you can pick the method and once you pick that method it runs the corresponding macro. Any ideas? I know it probably going to involve using Vb at some point.

Regards
Dave

 

[Smitty]

Welcome to the Board!

You can put your options in a Data Validation list. From there you can use a Change event that will be triggered when an item is selected from the list.

Here's some boilerplate Change event 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></FONT>

 

[davemc2016]

Welcome to the Board!

You can put your options in a Data Validation list. From there you can use a Change event that will be triggered when an item is selected from the list.

Here's some boilerplate Change event code:

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...)
************
End Sub

Thanks for this - i'll be honest i don't even know what a boilerplate change code is! My knowledge of Vb is basic. My data is set up like this: In column E1 an IF statement is running on certain specified dates and i'm currently using Advanced filter teamed with a macro to run them.

A1	B1	C1	D1	E1
Test <COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY> </TBODY>	Reagent Name <COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><TBODY> </TBODY>	Designation <COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY> </TBODY>	Expiry Date <COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY> </TBODY>	Status <COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY> </TBODY>
Geosmin & 2-MIB <COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY> </TBODY>	Working Internal <COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><TBODY> </TBODY>	Geosmin & 2MIB <COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY> </TBODY>	23/02/2015 <COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY> </TBODY>	Expired <COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY> </TBODY>
CW-PAH's <COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY> </TBODY>	CAL02 <COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><TBODY> </TBODY>	Indeno(123-cd)pyrene <COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY> </TBODY>	02/04/2015 <COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY> </TBODY>	Within 2 Weeks <COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY> </TBODY>

<TBODY>
</TBODY>