Running a macro from a drop-down list

USAMax

Well-known Member
Joined
May 31, 2006
Messages
855
Office Version
  1. 365
Platform
  1. Windows
I actually have a two part question of which I am sure the answer to the first question is no.

Can I create a drop-down list from a series of cells that has a blank cell or two in the middle?

DROP DOWN CELLS
Option 1
Option 2

Option 3
Option 4


My second question is why I am here. How can I execute a macro once a selection is made from a drop-down list?
 
For the first part you can create a non-blank list.

For the second you can use a Change event.

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,
 
Upvote 0
Thank you Smitty,

I have not written a change event but I think I get the idea. In your example is A1, B2, C3 a part of the list or the area that will affect the Change event?

If these cells trigger the event I assume Target.Count will be "1" for A1, "2" for B2 and "3" for C3?

Forgive me as this is a project I am starting next week and I am just doing the research now.

Thank you for your help,

Dave
 
Upvote 0
Yes and no,

The Set rng = ...("xxx") is the range that you want to evaluate for changes. E.G. Range("A:A") would look at all of column A.

The Target.Count >1 just makes sure that only one cell at a time is changed. So if someone comes in and copies a bunch of data into the range the code will exit.

If you want to evaluate multiple columns, like Range("A:C") and run different things based on that you can use a Select Case structure like this:

Code:
Select Case Target.Column
  Case 1 ' Column A
    '  Do something
  Case 2 ' Column B
    '  Do something
  Case 3 ' Column C
    '  Do something
End Select
 
Upvote 0
It looks like this project is going to start next week and the way I am planning to do this is very half-***.

The first column has 4 values with spacing from 130 to over 200 rows between them. The next column has 2 or 3 values associated with the values in the first column and this goes on for 7 columns.

I can fill in the values but then the drop-down list would have to display only the unique values. After the first column each list would have to display the unique values that match the previously selected value for the previous column.

I have been doing this for many years, 10 years professionally, but I have never seen anything like this.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top