Running a macro from a drop-down list

[USAMax]

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?

 

[Smitty]

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,

 

[USAMax]

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

 

[Smitty]

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:

Select Case Target.Column
  Case 1 ' Column A
    '  Do something
  Case 2 ' Column B
    '  Do something
  Case 3 ' Column C
    '  Do something
End Select

 

[USAMax]

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.